Append Table problem

Hi all,

I am having a problem appending data to an existing table. I have two
tables with identical fields and I want to append the data from one
table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
a difference of 73 records. When I run the append query, only 72
records are selected from T2, and there doesn't seem to be a pattern
as to why the records selected were selected. They aren't the last 72
records or the first. Can anyone tell me what I am doing wrong or if
they have had this happen before.

Thanks,

Wade

Re: Append Table problem

wade.wall@gmail.com wrote:
> Hi all,
>
> I am having a problem appending data to an existing table. I have two
> tables with identical fields and I want to append the data from one
> table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
> a difference of 73 records. When I run the append query, only 72
> records are selected from T2, and there doesn't seem to be a pattern
> as to why the records selected were selected. They aren't the last 72
> records or the first. Can anyone tell me what I am doing wrong or if
> they have had this happen before.
>
> Thanks,
>
> Wade

Is it possible you have an index that does not allow duplicates?

Re: Append Table problem

On Nov 30, 12:08 pm, Salad wrote:
> wade.w...@gmail.com wrote:
> > Hi all,
>
> > I am having a problem appending data to an existing table. I have two
> > tables with identical fields and I want to append the data from one
> > table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
> > a difference of 73 records. When I run the append query, only 72
> > records are selected from T2, and there doesn't seem to be a pattern
> > as to why the records selected were selected. They aren't the last 72
> > records or the first. Can anyone tell me what I am doing wrong or if
> > they have had this happen before.
>
> > Thanks,
>
> > Wade
>
> Is it possible you have an index that does not allow duplicates?

I had several fields indexed, with duplicates being okay. I removed
the indexing and still having problems. Returned the same 72
records.

Re: Append Table problem

wade.wall@gmail.com wrote:

> On Nov 30, 12:08 pm, Salad wrote:
>
>>wade.w...@gmail.com wrote:
>>
>>>Hi all,
>>
>>>I am having a problem appending data to an existing table. I have two
>>>tables with identical fields and I want to append the data from one
>>>table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
>>>a difference of 73 records. When I run the append query, only 72
>>>records are selected from T2, and there doesn't seem to be a pattern
>>>as to why the records selected were selected. They aren't the last 72
>>>records or the first. Can anyone tell me what I am doing wrong or if
>>>they have had this happen before.
>>
>>>Thanks,
>>
>>>Wade
>>
>>Is it possible you have an index that does not allow duplicates?
>
>
> I had several fields indexed, with duplicates being okay. I removed
> the indexing and still having problems. Returned the same 72
> records.

Is the query a "query" or are you creating the query in code? If
possible, open up your query in the query builder. Set it to a Select
criteria. I would think you have a filter on there someplace.

Perhaps post your SQL statement here, maybe someone can spot a problem.
Right now its simply guessing and by goshing.

Re: Append Table problem

wrote in message
news:7f73b3e9-81e5-42f8-849c-6e1200c98ade@o6g2000hsd.googleg roups.com...
> On Nov 30, 12:08 pm, Salad wrote:
>> wade.w...@gmail.com wrote:
>> > Hi all,
>>
>> > I am having a problem appending data to an existing table. I have two
>> > tables with identical fields and I want to append the data from one
>> > table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
>> > a difference of 73 records. When I run the append query, only 72
>> > records are selected from T2, and there doesn't seem to be a pattern
>> > as to why the records selected were selected. They aren't the last 72
>> > records or the first. Can anyone tell me what I am doing wrong or if
>> > they have had this happen before.
>>
>> > Thanks,
>>
>> > Wade
>>
>> Is it possible you have an index that does not allow duplicates?
>
> I had several fields indexed, with duplicates being okay. I removed
> the indexing and still having problems. Returned the same 72
> records.

Access usually provides a message when some records are not appended. Do you
possibly have SetWarnings set to False, or do you have query confirmations
turned off? The Access message is somewhat cryptic but it can give a
hint....
Fred Zuckerman

Re: Append Table problem

On Nov 30, 12:26 pm, Salad wrote:
> wade.w...@gmail.com wrote:
> > On Nov 30, 12:08 pm, Salad wrote:
>
> >>wade.w...@gmail.com wrote:
>
> >>>Hi all,
>
> >>>I am having a problem appending data to an existing table. I have two
> >>>tables with identical fields and I want to append the data from one
> >>>table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
> >>>a difference of 73 records. When I run the append query, only 72
> >>>records are selected from T2, and there doesn't seem to be a pattern
> >>>as to why the records selected were selected. They aren't the last 72
> >>>records or the first. Can anyone tell me what I am doing wrong or if
> >>>they have had this happen before.
>
> >>>Thanks,
>
> >>>Wade
>
> >>Is it possible you have an index that does not allow duplicates?
>
> > I had several fields indexed, with duplicates being okay. I removed
> > the indexing and still having problems. Returned the same 72
> > records.
>
> Is the query a "query" or are you creating the query in code? If
> possible, open up your query in the query builder. Set it to a Select
> criteria. I would think you have a filter on there someplace.
>
> Perhaps post your SQL statement here, maybe someone can spot a problem.
> Right now its simply guessing and by goshing.

First of all, thanks for the help.

I am building my query in design view. Each table T1 and T2, has an
autonumber field. I tried selecting the field sans the autonumber
from T2 to append to T1.

The SQL code is here:

INSERT INTO CollN1 ( Collection_Num, Old_Num, Species, Col_Date,
Location )
SELECT CollN2.Collection_Num, CollN2.Old_Num, CollN2.Species,
CollN2.Col_Date, CollN2.Location
FROM CollN1 INNER JOIN CollN2 ON CollN1.ID = CollN2.ID;

I am not an SQL expert, but the problem seems to be the inner join on
the ID field. Both tables have an autonumber field, and the query is
selecting those records from T2 where T1.ID=T2.ID. Should I get rid
of the autonumber column, or is there a way around that.

thanks,
Wade

Re: Append Table problem

wade.wall@gmail.com wrote:
> On Nov 30, 12:26 pm, Salad wrote:
>
>>wade.w...@gmail.com wrote:
>>
>>>On Nov 30, 12:08 pm, Salad wrote:
>>
>>>>wade.w...@gmail.com wrote:
>>
>>>>>Hi all,
>>
>>>>>I am having a problem appending data to an existing table. I have two
>>>>>tables with identical fields and I want to append the data from one
>>>>>table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
>>>>>a difference of 73 records. When I run the append query, only 72
>>>>>records are selected from T2, and there doesn't seem to be a pattern
>>>>>as to why the records selected were selected. They aren't the last 72
>>>>>records or the first. Can anyone tell me what I am doing wrong or if
>>>>>they have had this happen before.
>>
>>>>>Thanks,
>>
>>>>>Wade
>>
>>>>Is it possible you have an index that does not allow duplicates?
>>
>>>I had several fields indexed, with duplicates being okay. I removed
>>>the indexing and still having problems. Returned the same 72
>>>records.
>>
>>Is the query a "query" or are you creating the query in code? If
>>possible, open up your query in the query builder. Set it to a Select
>>criteria. I would think you have a filter on there someplace.
>>
>>Perhaps post your SQL statement here, maybe someone can spot a problem.
>> Right now its simply guessing and by goshing.
>
>
> First of all, thanks for the help.
>
> I am building my query in design view. Each table T1 and T2, has an
> autonumber field. I tried selecting the field sans the autonumber
> from T2 to append to T1.
>
> The SQL code is here:
>
> INSERT INTO CollN1 ( Collection_Num, Old_Num, Species, Col_Date,
> Location )
> SELECT CollN2.Collection_Num, CollN2.Old_Num, CollN2.Species,
> CollN2.Col_Date, CollN2.Location
> FROM CollN1 INNER JOIN CollN2 ON CollN1.ID = CollN2.ID;
>
> I am not an SQL expert, but the problem seems to be the inner join on
> the ID field. Both tables have an autonumber field, and the query is
> selecting those records from T2 where T1.ID=T2.ID. Should I get rid
> of the autonumber column, or is there a way around that.
>
> thanks,
> Wade

Yes. You have a "link line" between T1 and T2. Dbl-Click on it. Take
Option3, All records in T2 and matching in T1. Then drag T1's
autonumber into a column, turn ShowOff to false, and in the criteria enter
Is Null

Now it will select all records in T2 that have no records in T1.

Re: Append Table problem

On Nov 30, 12:53 pm, Salad wrote:
> wade.w...@gmail.com wrote:
> > On Nov 30, 12:26 pm, Salad wrote:
>
> >>wade.w...@gmail.com wrote:
>
> >>>On Nov 30, 12:08 pm, Salad wrote:
>
> >>>>wade.w...@gmail.com wrote:
>
> >>>>>Hi all,
>
> >>>>>I am having a problem appending data to an existing table. I have two
> >>>>>tables with identical fields and I want to append the data from one
> >>>>>table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
> >>>>>a difference of 73 records. When I run the append query, only 72
> >>>>>records are selected from T2, and there doesn't seem to be a pattern
> >>>>>as to why the records selected were selected. They aren't the last 72
> >>>>>records or the first. Can anyone tell me what I am doing wrong or if
> >>>>>they have had this happen before.
>
> >>>>>Thanks,
>
> >>>>>Wade
>
> >>>>Is it possible you have an index that does not allow duplicates?
>
> >>>I had several fields indexed, with duplicates being okay. I removed
> >>>the indexing and still having problems. Returned the same 72
> >>>records.
>
> >>Is the query a "query" or are you creating the query in code? If
> >>possible, open up your query in the query builder. Set it to a Select
> >>criteria. I would think you have a filter on there someplace.
>
> >>Perhaps post your SQL statement here, maybe someone can spot a problem.
> >> Right now its simply guessing and by goshing.
>
> > First of all, thanks for the help.
>
> > I am building my query in design view. Each table T1 and T2, has an
> > autonumber field. I tried selecting the field sans the autonumber
> > from T2 to append to T1.
>
> > The SQL code is here:
>
> > INSERT INTO CollN1 ( Collection_Num, Old_Num, Species, Col_Date,
> > Location )
> > SELECT CollN2.Collection_Num, CollN2.Old_Num, CollN2.Species,
> > CollN2.Col_Date, CollN2.Location
> > FROM CollN1 INNER JOIN CollN2 ON CollN1.ID = CollN2.ID;
>
> > I am not an SQL expert, but the problem seems to be the inner join on
> > the ID field. Both tables have an autonumber field, and the query is
> > selecting those records from T2 where T1.ID=T2.ID. Should I get rid
> > of the autonumber column, or is there a way around that.
>
> > thanks,
> > Wade
>
> Yes. You have a "link line" between T1 and T2. Dbl-Click on it. Take
> Option3, All records in T2 and matching in T1. Then drag T1's
> autonumber into a column, turn ShowOff to false, and in the criteria enter
> Is Null
>
> Now it will select all records in T2 that have no records in T1.
Thanks a lot. That helped me out.

Wade