I need somebody to write some code/query for an existing Access database. The coding needs to work for both 2003 and 2007.
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 (an accounts program). 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, but my record has up to 7 different products/quantities/prices The way Sage works, is that you must import the record a second/third/fourth time - keeping 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, I need a query (or soemthing) 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
... where the above comes from the same record.
The only way i can think of doing it is to run 7 seperate queries (one for each product line) and then merge the resulting datasheets afterwards. However, it doesn't have to be done like this - if you can think of a better way, then that would be fine.
The result needs to be one CSV file.