Append Query with Autonumber

[gstylianou]

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

 

[theDBguy]

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]

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]

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.