Special insert query - possible? 1

[VBAjedi]

This is gonna sound like bad dbase design (because it is!), but trust me, there's a reason. . .

Would it be possible to construct an insert query that looks through my "Rates" table for any records where the "Quantity" column is "1" and the "Type" column is "LTP". For each one it finds, it would insert nine more records, identical to the found record except that the quantity would increment 2-10, and the "Price" would be the price for the original record * quantity for the new record?

VBAjedi

 

[rac2]

Make a table with Quantity 2-10. JOIN it, ( a CROSS JOIN ), with the table you want to add rows to. INSERT rows in the table by SELECTing from the CROSS JOIN.

Rows to be added -

SELECT t.Quantity, t.Quantity * a.Price, a.other_columns
FROM Rates a, TempQuantities t
WHERE a.Type = 'LTP'

I assumed that all of the Rates rows have Quantity = 1, otherwise, you will have to add that to the WHERE clause.

Add the new rows -

INSERT Rates (Quantity, tPrice, a.other_columns)
SELECT t.Quantity, t.Quantity * a.Price, a.other_columns
FROM Rates a, TempQuantities t
WHERE a.Type = 'LTP'

Maybe that will work. Let me know.

 

[VBAjedi]

Sounds workable. Leaving now, but I'll try it tomorrow and post back if I have any problem. In the meantime, have a star for the suggestion. . .

VBAjedi