Append Query - Multiple records based on qty field

  • Thread starter Thread starter John V
  • Start date Start date
J

John V

I am looking for an easy way to append records to a table
or at least view records in a query where if the qty field
value is 2 it will show or append 2 records and if 3 then
3 etc...

Basically I am importing a CSV file that has products a
customer ordered. The qty ordered may be more than one,
but UPS Worldship software needs to import each package
seperately. THerefore I need to say if qty is 2 then in
the export file I create I need 2 records for the same
order.

I hope this makes sense... thanks in advance for your help.

John
 
Hi,


Have a table, Iotas, one field, Iota, with values from 1 to 1000 (or up to
what you need).


SELECT a.*, Iotas.iota
FROM myTable As a INNER JOIN Iotas
ON a.qty <= Iotas.iota



should do.


To build the table iotas, if it has to be large, first start with a table,
Ds, one field, d, and fill it with 10 records, with values from 0 to 9.

SELECT 1+u.d+10*t.d + 100*c.d As Iota
FROM Ds As u, Ds As t, Ds as c

would generate the data. Transform the query into a make table query, make
the table Iotas, and once done, add the primary key on Iota. Delete table
Ds.



Hoping it may help,
Vanderghast, Access MVP
 
Not exactly what I needed, but it gave me the insight to
get it working using your method... the ON statement was
reversed however...

Thank you very much for your help... It is appreciated.
 
Hi,

You are right, it should have been a.qty>=Iotas.itoa.


Vanderghast, Access MVP
 
Back
Top