Retrieving records from both source and linked tables

[Former Member]

Hi,

I'm looking for some help with a report I'm trying to create in my ordering and invoicing system. This report compares the items and prices on an order with the items and prices on the related invoice.

The order table contains the fields:

ORDER_NO	ITEM_NO	ITEM_DESC	PRICE
1	123456	Paper	2.00
1	234567	Staples	1.00

There are two invoice tables, one with header information and one with item information. The header table looks like:

INV_NO	ORDER_NO	TOTAL_VALUE
111	1	3.50

The line table looks like:

INV_NO	ITEM_NO	ITEM_DESC	PRICE
111	123456	Paper	2.00
111	345678	Different Staples	1.50

In this example, the paper is on both the order and invoice, but we ordered one type of staples and were invoiced for a different type of staples with a different item number.

I've created a report where I've linked the tables (the invoice table is the main table) using the Order number and the item number using a left outer join. The report contains: Invoice Number, Order Number, Item Number, Item Description, Invoice Price, and Order Price

This displays the two lines which were on the invoice - showing a null value in the Order Price column for the staples - but I'd like the report to also show the original order line, with a null value in the Invoice Price column.

Is that possible?

[Former Member · Accepted solution]

Bump, anyone able to assist?

[abhilash_kumar · Accepted solution]

Hi David,

You'd need to Join from the Invoice Header table to the Line Table on the 'INV NO' field via a LEFT JOIN.

You'd then need to Join from the Line Table to the Order table on the 'ITEM NO' field via a 'FULL OUTER JOIN'.

If you don't see the Full Outer Join option, you'd need to create a SQL Query and use that as the report's datasource.

-Abhilash

[Former Member · Accepted solution]

Thank you, I'll give that a try.