Re: Append Table problem

On Nov 30, 12:26 pm, Salad <o...@xxxxxxxxxxx> wrote:
wade.w...@xxxxxxxxx wrote:
On Nov 30, 12:08 pm, Salad <o...@xxxxxxxxxxx> wrote:

wade.w...@xxxxxxxxx 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
.

Relevant Pages

  • Re: Append Table problem
    ... When I run the append query, ... open up your query in the query builder. ... I tried selecting the field sans the autonumber ...
    (comp.databases.ms-access)
  • Re: Append Table problem
    ... When I run the append query, ... open up your query in the query builder. ... I tried selecting the field sans the autonumber ...
    (comp.databases.ms-access)
  • Re: Append Query Help!
    ... In SQL view the query was selecting from both tables ... "Chris" wrote: ... > start a new query and select append, then specify the table to append ...
    (microsoft.public.access.queries)
  • Re: Membership database updates
    ... When you open any Query in Design View, click on the toolbar just next to ... black down-arrow where you can change your query to an Append or Update ... If you have any current members in your Import list, ... You mention Update Queries and Append Queries but I can't find how to ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Assign macro on After update event
    ... I don't see why you can't append the current record next scheduled date to ... it then try using an update query to update the dates in the new record. ... "Anna" wrote: ... maybe you can help me with that: all this macro is ...
    (microsoft.public.access.forms)