Querie for inexisting records

[Guest]

My company builds custom designed products different from customer to
customer. Each order may refer to only one reference or several references
from a customer. I’ve got a table of ORDERS where I keep a record of the
Order, PK number type, manual input. This table is linked by this Order field
to a second table of REFERENCES where the Reference is PK, string type,
manual input. My Reports and Forms are working nicely. I also have several
orders without any product defined, because we have to wait for the
customer’s answer. This brings me to a problem: I want to list the Orders
without a reference attached. Is it possible to build a query like this? If I
specify in the query the REFERENCES table it automatically eliminates the
“empty†orders… What about completely delivered orders? I know I could define
a field on the ORDERS table like “Order closed†but an order may have 3 or 4
different references. It would be difficult to verify the order state through
its references. Each one may be delivered on quite different dates so I’ve
set a “Delivered†(Yes/No) field on each reference to “close†each one of
them. Any suggestions appreciated.

Thx all.

 

[Rob Oldfield]

You need (I think) a subtract query. You can actually get at these by using
the 'Unmatched' option when using the query wizard but I tend to think that
that's a bit of a cop out as using it means that you end up not
understanding how they work.

So...

Create a query containing both your Orders and References table. Link the
key fields have you have already done. Add the key field from both tables.
Run that and (as you correctly state) it eliminates any order without a
matching reference.

Double click on the line linking the two tables. You should see a 'Join
Properties' dialog. Change that to the 'Show all records from Orders'
option. (I can't remember the exact wording, but something like that
anyway.) Run that one.

You should now see that all of the Orders with no corresponding Reference
are now included. So the last step is basically to just exclude those ones
that do have a Reference. You should hopefully be able to see that that
basically is all those where the reference id contains some data.

So just switch back to the design and add a criteria of Null under the key
field of the Reference table. Run that and hopefully you have what you're
after.

 

[Guest]

Works like a charm! I'll try to learn something more about queries and the
subtract functions.

Thx a lot Rob.