Increment ID In Append Query

[Darxion]

How can I incrment an ID on the fly in an append query, if possible?

I have two tables with a one to many relationship between them (header table and body table). The header table has a primary key on the ID field. The body table has the primary key on the combination of the header table ID (HeaderID) and a body table ID (BodyID). I want to append to the body table with the correct header ID (no problem) and a body ID that increments for each record that appends for the same header ID (problem).

I know I could run the query and then update the BodyID field with code to increment the BodyID for the same HeaderID, but I wanted to do it all within one query.

I would guess this one is not possible in Access?

Header Table (read ID)
ID=X
Other information

Body Table (append to)
HeaderID=X
BodyID=Incrment by 1 from 1 to total number of records that match on other information for each X (will only be one X per run of query)
Other information...

 

[Quehay]

This is a procedural requirement, so you'd have to use VBA.
The code is very skeletal here..it's approach not usable code. Post again if this doesn't make sense!

Dim lngNewID as Long
Dim RsLastID as Recordset (your choice of type)
Dim RsRowsToAdd as Recordset


Set RsLastID = CurrentDB.OpenRecordset (Select Max(ID) from Body)

lngNewID = Rs!Fields(0)
lngNewID = lngNewID + 1


Do While Not RsRowsToAdd.EOF

Insert into TargetTable (H_ID,B_ID,OtherCol)

Values (HeaderID, lngNewID, OtherVal);

lngNewId = lngNewID + 1

(No Commit needed in Access)

Loop

 

[Darxion]

Thanks.........I had already chosen to use VBA similar to what you posted.