Create multiple records from a number field

[kballing]

I am working on a database for inventory of a laboratory. Currently there is a table that defines samples, 'Samples'. This includes a primary key, sample ID, subject demographics, among others, and a numeric field of test tubes belong to a sample 'TubeNumber'. This table must remain intact because I don't manage it and a lot of other important queries link to it. The inventory is getting out of hand and we now would like to track each test tube individually. For example, a patient may get blood drawn that results in 8 test tubes of plasma. I have created a table called 'Tubes' that will hold all the information specific to a tube.

What I want to do is to go through the existing Samples table and create X number of records in the Tubes table depending on TubeNumber. So for our exmaple some function would lookup TubeNumber, which is 8, and create 8 records in the Tubes table.

Is there a way to do this with a query, or do I need to write some VB code? I know it can be done with some for loops, any other functions that might be useful?

 

[RuralGuy]

AFAIK there is no query function that will achieve the results you are looking for. A loop in code is all I know to do.

 

[LPurvis]

Sounds similar to the recent thread on it here.
I have to re-iterate the caveat about choosing to do this though.
Records created in advance needs to be for a specific reason.
(i.e. preserving some set of data as if it were a template at the time of record creation that won't be affected by subsequent edits).

Otherwise you can just maintain an 8 row table as the "template" of records - use an Outer Join to your detail table and enter the rows as they are required to have detail assigned.
I can exapand upon that concept a bit if you like (I doubt it was very clear from that little diatribe there

Cheers.

 

[kballing]

Thanks for directing me to the helpful post LPurvis.

RuralGuy, check out the linked thread. Now you know.

I think I will use the query method using a 'template' table as described. I have already started writing some do loops, but would rather just use queries.

 

[RuralGuy]

Leigh knows his stuff. Good link.