append query with multiplier

[mmori]

I have table A, in which one of the fields is quantity. I want to append records from table A to table B, but in table B I would like there to be an independent record for each instance. So if table A contains item A, quantity=3, then table B would have item A in 3 separate records. How do I do this? Any suggestions?

[TheShabz]

I'm not the best at VBA code but I can give you an idea on how I'd attack it. But before I do, I'm interested in why you would want to store redundant data in a table? What purpose does it serve?

Anyway, you would open up table A as a recordset. you would iterate through all the QTY fields assigning the value as a variable. You would then have a For-Loop or Do-While statement that would go from i = 1 to i = rs.QTY doing an Insert Into query written on the fly from the values in the rest of the fields from table. Do Until rs.EOF

It's a pretty longwinded way of doing something that you probably shouldn't be doing in Access to begin with.

[mmori]

So to try and summarize what I'm trying to do, table A is a scope of work on a construction project. At the pricing phase there is a line item for 3 doors. That is suitable for preliminary costing. At the construction phase though those doors have additional data. They have different hardware, they may be painted different colors. Whatever it is. In fact, by the time we get down to construction there may only be 2 doors or we may have increased to 4 doors. The point is, each door has individual qualities beyond just price. Based on our work flow it doesn't make sense to keep track of each door individually in the initial stages, but it's necessary as we move through to construction.
I have looked at a few other posts and read about looping through the records, but I was hoping to stay away from that if there was some way to manipulate the query. Thanks for the help.

[TheShabz]

I'm not aware of any other way to do what you're describing outside of the loops in VBA as described earlier.

[pbaldy]

One option:

SELECT Whatever.Whatever, Counter.Counter
FROM Whatever LEFT JOIN [Counter] ON Whatever.Quantity >= Counter.Counter