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...
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...