Interesting situation. This sort of looks like a reverse crosstab
requirement to me, ie. generate the source table starting with a crosstab
query result. A couple of questions:
1.) Is this a one-time need, or an on-going need?
2.) Are there any duplicate entries for PartNum (or perhaps the combination
of PartNum + Length_m)? For example, somewhere later down in the table,
could you find an entry for: xxxx, 102, y, 5486, where xxxx is the
autonumber and y is the quantity? If so, I would think that you would want
to "add" the quantities together first, to create a unique record. Note: If
you can enforce a Unique multifield index on the PartNum and Length_m fields,
then you should be okay.
I would create a recordset in code that sorted by PartNum and Length_m
ascending. To prevent any accidental screwups to your data, you will want to
back up your database first. I'd also be tempted to write the results to an
empty table that is a copy of the structure of your existing table, rather
than write the results back to the same table (at least until you were
throughly satisfied of the results of extensive testing). So, you will open
two recordsets: one to read the unique records from the source table, and one
to write the results to the new table.
You will have a loop that will add Y number of records to the new table,
where Y = the Quantity value from the source table. You'll continue
processing the source table until rs1.EOF.
Here is a sample database that includes two examples of reversing a crosstab
like summary result:
Reverse Crosstab Query
Microsoft Access MVP