Duplicating records

[Simon]

I have produced a table of equipment type and quantity
with a view to sending it to another department so they
can load it on to there antiquated system.

They require 1 line for each item, e.g. if there a 5 of a
particular printer on one site they require 5 lines!

The data looks like this

OrigBillID Equipment Qty
MB-M40 2NL v.24 Terminal 1
MB-M40 2NL Teleservice Modem 1
MB-M40 2NL MT151 Printer 3
MB-M40 2NL MT150 Printer 3
MB-M40 2NL Spider Atto Bridge 4

Does anyone know how i can make a loop that will look at
the quantity, and if the quantity is greater than 1
duplicate the record that number of times.

Thanks in advance for any assistance

Simon

[Allen Browne]

Simon, you can do this without any code if you have a counting table (i.e. a
table that has a record for each record from 1 to the highest number you
will need). I'll assume this table is named tblCount, and has one field
named CountID, of type Number (size Long Integer).

1. Create a query that has both your table and tblCount.

2. If you see any line joining the 2 queries, delete it: it is the lack of a
join that gives you one record for each combination.

3. Drag the fields you want from your table into the query design grid. Also
trag tblCount.CountID into the grid.

4. In the Criteria row beneath the tblCount.Count field, enter:
<= [NameOfYourTableHere].[Qty]
This causes Access to limit the query to just the right number of rows,
depending on the value in the Qty field.

Rather than enter all the records in tblCount by hand, this function will
enter 1000 records for you. If you need more than that, increase the
constant before running it.

Function MakeData()
'Purpose: Create the records for a counter table.
Dim db As Database 'Current database.
Dim lng As Long 'Loop controller.
Dim rs As DAO.Recordset 'Table to append to.
Const conMaxRecords As Long = 1000 'Number of records you want.

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblCount", dbOpenDynaset, dbAppendOnly)
With rs
For lng = 1 To conMaxRecords
.AddNew
!CountID = lng
.Update
Next
End With
rs.Close
Set rs = Nothing
Set db = Nothing
MakeData = "Records created."
End Function

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Allen Browne's Database And Training - Perth, Western Australia.
Tips for MS Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Simon" <simo...@ntlworld.com> wrote in message
news:04c801c2d744$be62d010$a601...@phx.gbl...

[Simon]

Wow, you are the man Allen, works beautifully

Thanks very much for your expertise, nervous breakdown
avoided!

Kindest regards

Simon

>.
>