Need to repeat records

[tlove1966]

Hello everyone...I have a table that looks like this:

CustNo / BullCode / Doses
123456 / 123HO3214 / 3
321654 / 654JE1245 / 5
456789 / 478AY6549 / 10

and so on.

What I am trying to do is for each record that shows more than one dose as above, I need to repeat that line to match the number of doses

For example the first line I show you here needs to look like this when I'm done:

CustNo / BullCode / Doses
123456 / 123HO3214 / 3
123456 / 123HO3214 / 3
123456 / 123HO3214 / 3

Quite honestly at this point, I no longer care about the number of doses, I just need it to list it 3 times....

I'm certain there must be a way...Any assistance is greatly appreciated.

TL

[Rabbit]

You'll need to do some VBA coding, where do you stand on that?

[tlove1966]

I stand in the dark... :-)

[Rabbit]

Well, first you'll want to find a VBA tutorial and start learning the basics. After that, what you'll want to do is:

1) Open up a recordset that uses SELECT DISTINCT so you don't get duplicate records.

2) Use a Do Until recordset.EOF to loop through all the records.

3) Use a For i = 1 to recordset!Doses to loop the next command x times where x = number of Doses.

4) Use a DoCmd.RunSQL "Insert ..." to insert a copy of the record.

5) Increment the recordset.

So, read up on VBA, test out some stuff, and once you get started, we can help you through any implementation issues you have.

[tlove1966]

Great...thank you...I'll see what I can come up with....