Subquery

  • Thread starter Thread starter Peter Cole
  • Start date Start date
P

Peter Cole

I have 2 tables one for samples which has an AutoNumber
ID field and an orderNoId field which is related, many to
one, to an Order table by OrderNoID field and contains an
OrderPlaced field.

I need a query that returns all samples that have an
orderNoId of 0 (not ordered) and all that have an
orderIDNo greater than 0 where Orderplaced is true.

I don't think I can use a UNION as they are not updatable
are they?? I have tried a number of sub queries without
sucess. Thanks in anticipation
 
Create a query with the 2 tables in the query design grid.

In the Criteria row under the OrderNoID field, enter: 0
In the Or row (the one below Criteria), enter: >0
In the same Or row, under OrderPlaced, enter: True

The crucial thing is to get the AND and OR right. You will end up with a
WHERE clause something like this:
WHERE ((Order.OrderNoID = 0) OR ((Order.OrderNoID = 0) AND
(Order.OrderPlaced = True)))

Post back if I have not understood you correctly.
 
Thanks Allen you understood but still a problem.
If tables are joined on order no then the query does not
give results for where Order no is Zero since there is no
entry in the order table. Using with join deleted Uniq
values or uniq records don't give the desired result.
(would not want to use uniq values are not updateable)
Thinking of putting a dummy order in with number of 0.
Thanks Peter
 
Not exactly clear on the issue, but would an outer join work?

In the query design window, double-click the line joining the 2 tables.
Access offers a dialog with 3 options.
Try #2 or #3, depending which way you want the data.
 
Outer Join worked great. Thanks Allen
-----Original Message-----
Not exactly clear on the issue, but would an outer join work?

In the query design window, double-click the line joining the 2 tables.
Access offers a dialog with 3 options.
Try #2 or #3, depending which way you want the data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
 
Back
Top