How do I Identify records that have not been created yet?

[JohnB]

Hi,

I have been working on this issue for several weeks without success, any assistance would be much appreciated.

My database tracks the invoices related to shipments, each shipment can have as many as 20 different invoices raised against it, but not all shipments have the same invoices.

I have a query which shows all the invoices which are required for each shipment and I have a table which holds the data pertaining to each invoice as a record. Both the query and the table have a Shipment ID field allowing joins to be made.

I am trying to develop a query which will show the details of each "Expected Invoice" for the shipments AND where the invoice record has not yet been created a flag to show that that invoice is expected, but has not yet been raised (ie there is not yet a record for it in the table).

I have tried all sorts of joins and all sorts of approaches, but the best I have achieved is a list of the shipments which shows the invoices that have been raised and therefore have a record in the table. This even happens when I state that all records from the expected invoice query are to be shown in the join properties.

Can anyone tell me what I have to do to identify records which have not been created yet, ie if there is no record in the table, but the query says an invoice is expected tell me the invoice record is missing.

The "Expected Invoice" query reports each shipment as a row and each possible invoice as a column.

The "Invoice" table shows each invoice as a record with 2 fields identifying the type (Freight, Product, Tax etc) and sub type (Initial, Final, Supplementary) of invoice and a field which identifies which shipment it applies to.

Any help you can give me would be much appreciated.

 

[JohnB]

Pat,

Thanks for your reply, I have tried that and it does not seem to want to play. It still insists on missing shipments which show in the Query out!

Kind regards

John

 

[The_Doc_Man]

What you really want is perhaps a query where the DCount of entries in your invoice table for corresponding shipments is zero.

I.e. for each entry in a shipment table, you have a key that links the shipment to one or more invoices. The invoices are in a separate invoice table. So write a query that lists all shipments for which there are zero entries in the invoice table having that shipment's key. Which is a DCount function, and you can look that one up in Access Help.

 

[JohnB]

Doc Man,

Thanks for your response, I have been thinking this through and if I understand you correctly the query would report shipments with no invoices.

My problem is that each shipment may have any number of invoices (from 1 to 20) and every shipment is different. I am trying to design a query that shows that for shipment x, y invoices are expected and only z are there. It must also tell me that the missing ones are for example the Initial Freight invoice and the final insurance invoices. I'm not sure how the DCount function achieves this.

 

[JohnB]

As a follow up to my initial question, I spoke to a friend who suggested that the only way to resolve this was by using a Union Query which tied together the expected invoices and those that had arrived.

This was a great solution and allowed me to develop a form which shows the expected invoices which have and have not yet arrived. I then use conditional formatting on the fields in the form to tell the user when action such as chasing or paying the invoice is required.

I was delighted with the result, but when I split the database to place the B/E on the server and the F/E on the (2) user’s desktops the whole thing fell over. It would appear that the query exceeded the number of links permitted between the queries and tables. (The query links about 20 separate queries, which in turn are based on others which have links to multiple tables.)

My new question then is; Is it possible to place the union query, all associated queries and of course the form on the B/E and allow the users to call the results from their F/E switchboards? My thinking is; if the form works well when I call it from the server in an un-split database, running it in the B/E might work.

The problem is that at a mere amateur I do not know if this is the best way of doing this or even how to do it.... please could anyone help?