Creating a multi-line invoice

[Hfnet]

I have been asked to create proforma invoices in an access report. This is fince, but there are extras on the proformas that I can't figure out:

Each item is listed in a table, so I call those items in a subform in the report body, no problem there...

BUT some items have links to another table, and this is where I am having trouble.

Some items are upgraded, for example, so line 1 of the invoice may be for product x, but has been upgraded. These lines are in a separate table and may incude multiple entries, so I would need

Line Item Qty Qty To Price Cost
Ordered Dispatched Follow Each
1 Product Y 1 1 25.99
2 Product Z 2 0 2 57.87
3 Product X 1 1 128.00
upg from Prd
V, extra RAM

where the last two lines are in a different table and in two separate records. These are what I can't figure out how to populate...

 

[Remou]

If you have a link, can't you use DlookUp?

 

[BSman]

Can you use a query that does not require a match from the detail table? So if there is no record in the "upgrade" table, it will just return null values for those lines.

Bob S.

 

[Hfnet]

Bob,

Can you give me a pointer to the query? I have tried but the best I can do is have the upgrade details listed under every line item, instead of the one line number matching the upgraded items

 

[BSman]

You should be able to create the query using the Access query builder. Just select the two tables, link the appropriate column(s), right click each link line and select the option that will return all rows in the invoice line details table and any matching rows in the line upgrade table.

Bob S.