Batch record creation (1 Viewer)

[Squiddley1957]

I guess that this shall require some sort of nested For Next loop? I want to set a number of iterations of six records. For example 8 or 10 sets of six records - each set to acquire an autonumber ID from a “set” table and each record to have a field in which numbers 1 to 6 shall be inserted on creation. Can anyone point me towards code that I can adapt for this purpose? Much obliged.

 

[theDBguy]

Hi. There are a few ways to go about this. Can you tell us where the records your trying to create will be coming from?

 

[Pat Hartman]

What data is being used to group the sets? Wouldn't that be the equivalent of a batch number? If you are arbitrarily chopping a set of data into stanzas of 6, you have to do it with two loops. The outer loop creates the batch record and saves it. The inner loop selects the "next" six records and updates them to the batch number you just created. When the inner loop finishes, the outer loop, loops.

 

[Squiddley1957]

The nested loops us what I think will work. Last time I did one was in BASIC on a Spectrum

 

[theDBguy]

The nested loops us what I think will work. Last time I did one was in BASIC on a Spectrum

But a loop may be an overkill if a query can do it.

 

[Squiddley1957]

Hi. There are a few ways to go about this. Can you tell us where the records your trying to create will be coming from?

There are eight or ten employees each of whom conduct six interviews per day. Interviewers are in a separate table linked via FK. Interviewees in another table and added ti the interview record via a combo box.

 

[Squiddley1957]

But a loop may be an overkill if a query can do it.

An append query? But how do I add the 1,2,3,4,5,6 to the records and then repeat to get the eight sets? String of append queries?

 

[theDBguy]

An append query? But how do I add the 1,2,3,4,5,6 to the records and then repeat to get the eight sets? String of append queries?

If you can give us a more detailed description of your setup (for example, post a sample copy of your db or screenshots), then we can tell you exactly how to do it. For example, if I had a table of the standard set of records 1 through 6 that I want to add to another table for additional data, then I might use the following query.

INSERT INTO OtherTable SELECT * FROM StandardsTable

 

[Squiddley1957]

I haven’t put it together yet except on paper. I’m thinking four related tables ie tblinterview (to which I want to add the batch), tblinterviewDate (dateID is fk in tblinterview), tblinterviewer, and tblInterviewee (both have ID FK’s in tblInterview). I just want to create 8x6 records each day with each subset numbered 1-6.,

 

[theDBguy]

I haven’t put it together yet except on paper. I’m thinking four related tables ie tblinterview (to which I want to add the batch), tblinterviewDate (dateID is fk in tblinterview), tblinterviewer, and tblInterviewee (both have ID FK’s in tblInterview). I just want to create 8x6 records each day with each subset numbered 1-6.,

Okay, like I said earlier, there should be several ways to approach what you want to do. So, just don't get yourself stuck on focusing on one single approach. Once you have it sort of working or built and you can't get it to do exactly what you want, you can post your code or your db, so we can help you fix it. Good luck!

 

[Pat Hartman]

Now it sounds like you want to add a sequence number as the interviewees are added to the table. It is almost always wrong to add "empty" records ahead of time. Are you getting a file of interviewees that you have to divide and assign to a specific individual? If so, then my original suggestion will work. If you are adding the interviewee records one at a time as the people come in the door (so to speak), then you will add a sequence number in the BeforeUpdate event of the form. No update query or code loop will be required.

DBGuy,
Queries can do one thing to every row of a recordset. They cannot do multiple "things". So a single query cannot both create a batch record in one table and then update some set of records in a different table with that new batch number.

If Squiddley is calling 1,2,3,4,5,6 and then repeat a "batch" that is different To me a batch means a way of grouping records arbitrarily such as the first six records are assigned to batch 1, the second 6 to batch 2, the third 6 to batch 3, etc. Using that concept, you first create the batch number record and save it (outer loop) and then assign that same batch number to the next six unassigned records.

I guess we'll need to wait for Squiddley's definition of "batch"

 

[theDBguy]

DBGuy,
Queries can do one thing to every row of a recordset. They cannot do multiple "things". So a single query cannot both create a batch record in one table and then update some set of records in a different table with that new batch number.

Sorry, I didn't fully understand the OP's intent. If the OP needed to update multiple tables, then they'll definitely need to use more than one query. Cheers!

 

[Squiddley1957]

Now it sounds like you want to add a sequence number as the interviewees are added to the table. It is almost always wrong to add "empty" records ahead of time. Are you getting a file of interviewees that you have to divide and assign to a specific individual? If so, then my original suggestion will work. If you are adding the interviewee records one at a time as the people come in the door (so to speak), then you will add a sequence number in the BeforeUpdate event of the form. No update query or code loop will be required.

DBGuy,
Queries can do one thing to every row of a recordset. They cannot do multiple "things". So a single query cannot both create a batch record in one table and then update some set of records in a different table with that new batch number.

If Squiddley is calling 1,2,3,4,5,6 and then repeat a "batch" that is different To me a batch means a way of grouping records arbitrarily such as the first six records are assigned to batch 1, the second 6 to batch 2, the third 6 to batch 3, etc. Using that concept, you first create the batch number record and save it (outer loop) and then assign that same batch number to the next six unassigned records.

I guess we'll need to wait for Squiddley's definition of "batch"

I’m not creating empty records - I’m looking to save time by populating some of the fields in a set number of records. If any of the other fields remain unpopulated then I can easily pick up on that. I think that I shall create a table with the desired number of records etc and run an append query. Thanks for help everyone.

 

[theDBguy]

I’m not creating empty records - I’m looking to save time by populating some of the fields in a set number of records. If any of the other fields remain unpopulated then I can easily pick up on that. I think that I shall create a table with the desired number of records etc and run an append query. Thanks for help everyone.

Hi. Good luck with your project. Let us know how it goes.

 

[Pat Hartman]

Sorry, I can't tell what you are trying to do or when you need to do it. Is this something you want to do as you are adding records via a form? Is this some kind of fix up that you want to do to existing records? Are you really looking for a batch number or are you looking for a way to assign a sequence number within some group? If so, how are you defining the group? Why are you doing this after the fact? Why doesn't it happen as the records are being added?

I've suggested two different solutions. You'll have to tell me which is of interest to you as well as define your concept of a "batch".