Append Query with Autonumber (1 Viewer)

gstylianou

Registered User.
Local time
Today, 22:23
Joined
Dec 16, 2013
Messages
357
Hi,

Attached there is a file for better understanding on what exactly I should do:

In my database there are two tables, the Table1 (Master Table) and the Table2 (Child Table). Usually i have new records form one external file (accdb) which are included into Table2.

What i must to do is the following:

1. The Table1 has one autonuber field and another one field with the ID of each record.

2. Because the Table2 is imported from another (external) database with a different design architecture, etc., does not include the ID needed in the Table1, Therefore it cannot append the new records into Table1 without this ID. As a result of this issue i must use other methods (such as ecxell) in oder to give the appropriate ID numbers for the field [ID] into Table1.

So, what i need is to modify the append query so to give directly the [ID +1] based on last number into ID field.

I tried to put an expresion field into the append query to give the corect numbers for the [ID] field (+1) without any results..!

I would apprechieate any help

Thanks in advance
 

Attachments

  • Αύξουσα αρίθμηση.zip
    18 KB · Views: 55

theDBguy

I’m here to help
Staff member
Local time
Today, 13:23
Joined
Oct 29, 2018
Messages
21,358
Hi. Just to make sure I understand, the Table2 in your attached file includes a sequential ID column. Will this always be available in your imported table? If so, will it always start at 1?
 

gstylianou

Registered User.
Local time
Today, 22:23
Joined
Dec 16, 2013
Messages
357
Hi. Just to make sure I understand, the Table2 in your attached file includes a sequential ID column. Will this always be available in your imported table? If so, will it always start at 1?

Not, exactly the opposite.!

Example¨

Table1
Auto: 1 ID: 100100
Auto: 2 ID: 100101

Must take the records from Table2
Auto: 1 ID: 100100
Auto: 2 ID: 100101
Auto: 3 ID: 100102
Auto: 4 ID: 100103
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:23
Joined
Oct 29, 2018
Messages
21,358
Not, exactly the opposite.!

Example¨

Table1
Auto: 1 ID: 100100
Auto: 2 ID: 100101

Must take the records from Table2
Auto: 1 ID: 100100
Auto: 2 ID: 100101
Auto: 3 ID: 100102
Auto: 4 ID: 100103
So, if the sample file you provide doesn't exactly reflect the actual scenario, then can you post a new file showing exactly what you're dealing with? If there is a way to uniquely identify the records in Table2, and it doesn't have to be a sequential number (unless you care about the order in which those records are appended into Table1), then I can think of a way to dynamically create the ID number for you by "counting" each record in Table2.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 19, 2002
Messages
42,974
When you run an append query against a table with an autonumber primary key, you normally do NOT include the autonumber field in the append. You leave it out completely. The ONLY time you include the autonumber field is if you actually have a value you want to provide to use as the autonumber rather than have a new one generated. This method is used for conversions or re-adding previously deleted records but almost never used for adding new records. If you have an existing PK value, you should be using that as the PK rather than using an autonumber.
 

Users who are viewing this thread

Top Bottom