Create a summary form which displays the fact that a record is missing.

[JohnB]

Hi,
I'm developing a database with which to manage shipments of materials. This process involves a large number of invoices changing hands, some incomming and others outgoing. I normalised the DB and have a table called invoices which has the following fields;

Invoice ID (Auto Number)
Shipment ID (Linked to Shipment Table)
InvoiceType (Freight, Product, HarbourDues, etc),
SubType (Initial, Supplementary, Final, Credit)
WhenReceived
CheckedBy
Amount
ValueDate
PaymentReference
​

I now wish to have a summary Form which shows me the status of each invoice. Ideally this would look something like;

Freight - Initial - received - checked by - etc
Freight - Final- received - checked by - etc
Product - Initial -received - checked by - etc
Product - Final- received - checked by - etc
HarbourDues - Initial - received - checked by - etc
​

The problem is that if the invoice has not been presented yet there is no record of it in the Invoice Table and therefore it does not show on my summary form. In other words it would look like this;

Freight - Initial - received - checked by - etc
Freight - Final- received - checked by - etc
Product - Final- received - checked by - etc
HarbourDues - Initial - received - checked by - etc
​

How can I make the summary form show that an invoice has not been presented ie;

Freight - Initial - received - checked by - etc
Freight - Final- received - checked by - etc
Product - MISSING
Product - Final- received - checked by - etc
HarbourDues - Initial - received - checked by - etc
​

Any help would be much appreciated.

John

 

[maxmangion]

Hi,

in your query you need to use an outer join instead of an inner join.

 

[JohnB]

Maxmangion,

Thanks for your rapid response,

Unfortunately I'm not sure this will help as at the moment the Invoice Table is linked to the Shipment Table by the shipmentID. There is no list of expected invoices!

I believe there needs to be a list of the invoices that should be expected for each shipment (against which to compare the invoices in the Invoice Table). I tried a standalone table with InvoiceType and Subtype as fields, but then could not work out how to link it.

What I'm really asking is how to specify the records that should be there in order that the ones that are not can be identified.

Any ideas?