datasheet view - multiple rows per record

[jd_boss_hogg]

Hi All - I don't even know how to start creating this query - any advice would be much appreciated !

I have a table, which has invoice data, and each record can have upto 9 seperate products/qty/prices on the record (e.g., [product1][qty1][product2][qty2] etc) I need to output this as a CSV file, to import into Sage. I'm running a query, and then in datasheet view, saving this as a delimited text file. All works well.

here is the problem. Sage doesn't allow a line of the CSV to include more than one product/qty/price etc. the way Sage works, is that you must import the record a second time - keep the invoice number the same - but change the product details, and then Sage appends all of the rows that share the same invoice number onto one 'Sage invoice'.

So, how can i run a query that displays a record in datasheet view, then re-displays the same record a line below where the criteria justifies it? Eg, if qty2>0

E.g., in datasheets i want...

ABC Ltd inv12345 10/1/09 Widgets 10.00 1
ABC Ltd inv12345 10/1/09 Donuts 12.00 1

Hoping someone can shed a little light for me !
Thanks!

 

[jd_boss_hogg]

BTW - the only way i can think of doing this, is to run 9 seperate queries with 'if QTYn >0' criteria, export each one as a CSV, then merge them all together !

However, spotted something else - if i add the same table to the query (so the table is in the query twice), then i Kinda get multiple rows per record, but can't seem to control this as it porduces thousands of copies...

 

[jd_boss_hogg]

Still stuck on this one - gonna have to pay to get a professional in to sort it for me unless i can come up with a workwround - would REALLY appreciate any advice....

thanks

 

[DCrake]

Have you got the invoice order details in a seperate table from the invoice header table. You should have.

You then need to create a query linking the PK from the invoice header to the FK of the order details table. Then bring down the relevant details.

If you do not and all your order details are in the same table as your order header then this is a typical case of denormalisation. Get your tables sorted out first.