Duplicating a record, “sort of”

[TanMan]

Here is what I am trying to accomplish


I have a table with information that was imported from another program.

The table has a quantity field and a price field.

Example quantity is 3 and price $15.00. I need to end up with 3 records that has a quantity of 1 and at price of $5.00

In other words I need to use the quantity field to tell the program how many records to make where the quantity would be 1 and the price would be divided by the quantity

3 records, where quantity is 1 and price is $5.00

What would be the best way to approach the problem I thought maybe a record set and put the new rows in a temp table, but really not sure what is the best way to go

Thanks

 

[CJ_London]

I would create a bit of code along these lines

Dim rst as recordset
Dim i as long
 
Set rst=currentdb.openrecordset("SELECT * FROM myTable")
While not rst.eof
    for i=1 to rst.fields("quantity")
        currentdb.execute("INSERT INTO ..... 1, Price/Quantity ....")
    next i 
    rst.movenext
wend
Set rst=nothing

 

[namliam]

or simply make a "dummy" table that contains one "1" record, two "2" records, three "3" records, four "4" records, etc.
I.e.
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
etc...

and join that to your original data source it should multiply the number of lines to work the way you want all you need to then do is to do Price / Quantity to get the unit price.