How to Insert Records with Incrementing Nos. in a field

[Guest]

I have a situation where I have to insert (94) records into a child table,
where all the fields will have the exact same info, except for one field
which represents the line no. from the master table, and I only need to do it
for lines 8-102..

Example (*key fields):
Master table:
ProjectID*, Item*(incrementing), other fields
12345 8 a;dlfjdl;jk
12345 9 a;dlfjdl;jk
12345 10 a;dlfjdl;jk
.... ... ...

Insert to Child table, for Items 8-102 above
Child table
ProjectID*, Item*, CycleNo*, Date
12345 8 1 11/02/2004
12345 9 1 11/02/2004
12345 10 1 11/02/2004
12345 ?... 1 11/02/2004

The records for Items 8-102 need to be created, will have the exact same
project, Cycle and Date values, but the Items have to number 8-102.

Can this be done with an Append or Update query, or do I have to set up
recordset code to do this 'intelligently'?
Thanks.

 

[[MVP] S.Clark]

There's is not a function in Access that will perform this task, thus code
is needed. DMax() can help you retrieve the max no per project, but it is
not a failsafe in a multi-user environment.

Use code to make an intelligent method of locking, retrieving, incrementing,
unlocking.

 

[Guest]

SOLVED:

I actually figured it out using an Append, anUpdate query, and another Append.

First I made a copy of the live child table, and then I removed the keys
from it, as I needed to append 2 out of 3 key fields to it.

Then, I appended just the ProjectID and Item keys from the master, with
criteria of Items 8 - 102, to the copychild table. That created 92 new lines
in the child table with just the project ID and the incrementing nos from the
master for Items. I still needed Cycle (a key field) and date, but they were
going to be the same for every record.

Then, I did an Update query against my copychild to update the Cycle to 1
and the date to 11/02/2004. Once my copychild table was complete, records
for Items 8 - 102, with the same proj, cycle, and date.

Lastly, I Appended from my copychild table to the live child table, and they
were all inserted with their keys of ProjectID, Item, and Cycle nos.

It was faster than it sounds.