Query Question! 2

[sd110404]

Hello All Access Query Gurus,

Table : Order
Fieldnames : Order ID, Customer Name, .....
Sample Data:
OrderID CustomerName Shipping Info .....
-------- --------------- ---------------
A0001 Customer1 abc
A0002 Customer2 xyz
A0003 Customer3 efg


Table : OrderItem
FieldNames: OrderItemNo, Order ID, Ordered Sq Feet, Unit price, RefKey,...
Sample Data :
OrderItemNo OrderID OrderedSqFeet Unitprice RefKey
------------ ------- -------------- --------- --------
1 A0001 3000 1.99 41
2 A0001 2500 1.99 42
3 A0001 1000 1.99 41
4 A0002 3000 1.99 41
5 A0003 1200 2.99 41
6 A0003 1230 1.23 42



Where Refkey is frm Reference Table , which has the following data :
RefKey Ref Desc
------ ----------
41 Sample
42 Flooring
49 Tax
51 Deposit
60 Payment

and so on...

Order and OrderItem table are One to many relationship.

I want all the orders listed whose refKey = 41 or 42 and if a order has 41 and 42 then only 42 should get displayed. And if only 41 for an order then only 41 should get displayed....

So frm the above sample datas, when i run the query , i should get an output like the following:

OrderID OrderedSqFeet Unitprice RefKey
------- --------------- --------- --------
A0001 2500 1.99 42
A0002 3000 1.99 41
A0003 1230 1.23 42

I tried with IIF() and i guess the way i used was wrong. I would really appreciate if anyone can give any info on writing this query in Access.

Please help.
Thanks in advance.
Mary.

 

[dhookom]

You should create a form with a multi-select list box. Then use functionality like found in faq703-3936 to open the report with just the selected items from the list box.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PHV]

You may try something like this:
SELECT OrderID, OrderedSqFeet, Unitprice, RefKey
FROM OrderItem WHERE RefKey=42
UNION
SELECT OrderID, OrderedSqFeet, Unitprice, RefKey
FROM OrderItem A WHERE A.RefKey=41
AND NOT EXISTS(SELECT * FROM OrderItem B WHERE B.OrderID=A.OrderID AND B.RefKey=42)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sd110404]

Thanks a ton PHV!
Your query was really very helpful.

Thanks again for your valuable time you spent.