Question need to create query from a table

Randy

Registered User.
Local time
Today, 18:40
Joined
Aug 2, 2002
Messages
94
I have an old ERP system. The upload function is one line per value. So if I want to upload 7 models I need 7 lines, not one line with Qty of 7
however my source system sends the data as one line with Qty of 7

DLM2245/17 DLM2245/17X 1
DLP2204/17 DLP2204/17X 7
DLP2249/17 DLP2249/17X 7
DLV1004/17 DLV1004/17X 147
DLV1005/17 DLV1005/17X 158
DLV1009/17 DLV1009/17X 187

so in this example line 1 is fine. But line two needs 7 lines, and line 3 needs 7 lines and line 4 147 lines etc etc etc
so this is how the first three lines would look in the query.
DLM2245/17 DLM2245/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X


any help is appreciated.
 
Where exactly does Access fit into this?
Can you consider these as transactions

eg DLV1004/17 DLV1004/17X 147

where 147 indicates the repeats? If so, fairly straightforward, but where do the individual records go?

What exactly is this DLV1004/17
 
field one, is Modelin (this is the model we received into repair facitly)
field two is Modelout (this is the refurbished model after repairs)
Field three is Qty (i.e the number of models we refurbished)

so in line 2 we received in 7 DLP2204/17 and repaired them to a DLP2204/17X

but our old ERP system requires one line for each occurrence, so in this example 7 lines with just the Modelin and Modelout i.e.
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X



The data from the repair system is loaded as
(1) modelin text 15
(2) modelout text 15
(3) qty number, standard,0 decimals
those are the three fields

and what I would like is a query that creates one row for each qty, and yes it is identical to the original row.

so TBLdata has these rows
DLM2245/17 DLM2245/17X 1
DLP2204/17 DLP2204/17X 7
DLP2249/17 DLP2249/17X 7


Qryupload needs this result
DLM2245/17 DLM2245/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X
DLP2204/17 DLP2204/17X

DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
DLP2249/17 DLP2249/17X
 
Last edited:
Only thing I can think of is to create a numbers table and then join the two tables where the number is <= the qty.
 

Users who are viewing this thread

Back
Top Bottom