Replace one multiple quantity record with multiple records

[Guest]

I have a table with fields: RecNum (AutoNum), PartNum (text), Quan, Length_M.
A example record has entries: 2,102,27,5486, respectively in these four
fields.
I would like to programaticaly change this one record to 27 records having
entries: xxxx,102,1,5486 in these four fields.
Suggestions would be appreciated.

 

[Guest]

Hi George,

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
http://home.comcast.net/~tutorme2/samples/revXtab.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Thanks Tom,
It is an ongoing need and there are no duplicates as you asked. Periodically
I need to import a new table having quantities >1 and then do the
split/replacement thing.
I have downloaded the reverse crosstab db and will try that approach.
I appreciate your help!

 

[Guest]

Hi George,

It's been a few years since I've spent any real time looking at that sample
(I checked it out briefly yesterday, before making it available). The sample
I posted is more complicated than you will likely need, since it converts a
crosstab query heading (date) into a date with the first of the month. In
your case, the field names can remain the same. If you are good at
reverse-engineering this example (add a break point and step through the code
using the F8 key), then I think you'll have enough to proceed. If not, post
back, and if possible upload a .mdb file with some actual data that can be
used for testing.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________