I have to write a view that will return all invoices, but with the contact person of the last invoice issued for the same product.
It is hard to explain so I will try to show on example:
Table1: "Invoice"
Table2: "Invoice_row"
Table3: "Contacts"
Each Invoice record contains Contact_id field which points to the contact person who order it.
Each Invoice_row record contains product information.
Products are based on codes (1100, 1101, ...) and to make things a bit harder, products have updates which have the same base number: 110001, 110002, 110101, ...
I would like to get:
Company, Product, Contact, InvoiceNo, InvoiceDate
I can get this with the contact person stated on the individual invoice record, but I don't know how to get the last contact person for each product:
If John ordered 1100 and then Jane ordered 110001 (which is the update to 1100 and both of them are contacts for them same company) I would like to get:
InvoiceNo 1, Product 1100, Contact person Jane
InvoiceNo 2, Product 110001, Contact person Jane
Makes any sense? I want Jane to be returned for all records.
I realy need this so I would appreciate any help.
If something is not clear I will try to explain again.
-thanks