Append Query - Multiple records based on qty field

[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

 

[Michel Walsh]

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

 

[Guest]

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.

 

[Michel Walsh]

Hi,

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


Vanderghast, Access MVP