Insert SQL from invoice report

[Wysy]

Hi,
I am doing invoicing report based on a table that contains transaction details IDs, dates and customers.
Every report must have an serial id number. In order to keep invoicing in order i need to have a table that keeps a track of the invoice ids along with the transaction details ID every invoice holds. So it1s a table that have TransactionDetailsID with the same InvoiceID.
I have succeeded with an INSERT INTO tbInvoices (InvoiceID, TransactionDetailID) VALUES (InvoiceID, TransactionDetailID) using the report fields only if there is only one transaction per customer. However i do not have a clue how to do it if there are many transactions per customer.
thanks

 

[Mark_]

Is your "Report" the same as an Invoice? If so, you would normally create an invoice record FIRST with transaction details as child records.

Depending on business model, one invoice can either be for a given sale OR for when items for a sale are shipped. Either case, create the invoice record first, attach the transactions to it, then print by invoice.

If you are dealing with multiple "transaction" but not all are shipping at once, you will need to identify those that are to ship prior to attaching them. MOST businesses would be printing packing slips for complete or partial invoices rather than creating an invoice based off of what they ship though.

 

[arnelgp]

you only need to save the InvoiceID on tblInvoices.
use this field as the Link Master/Child Field, if you have sub report.

 

[Wysy]

Thanks! It solved it!