Unable to select unique parts from products in queary 2

[CliffordTheRedDog]

I have a table with three fields.
Product, Part Number and Quantity.
I have created a form to select two products by using combo boxes. comb1 and combo2 The query then needs to shows parts that are unique to each product selected above.

SELECT  [Location Table].[Product Number], [Location Table].[Part Number], [Location Table].Qty
FROM [Location Table]
WHERE ((([Location Table].[Product Number])=[forms]![CSSimultaneousPanels]![combo1])) OR ((([Location Table].[Product Number])=[forms]![CSSimultaneousPanels]![combo2]))
ORDER BY [Location Table].[Part Number];

This sql brings up both sets of parts but I cannot achieve parts that are unique. Despite trying various ways to expand on this code, any pointers would be much appreciated.
TIA
Cliff

 

[mp9]

Try SELECT DISTINCTROW ...

http://www22.brinkster.com/accessory/

 

[PHV]

What about this ?
SELECT DISTINCT [Product Number], [Part Number], .Qty
FROM [Location Table]
WHERE [Product Number]=[Forms]![CSSimultaneousPanels]![combo1] OR [Product Number]=[Forms]![CSSimultaneousPanels]![combo2]
ORDER BY [Part Number];


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

 

[CliffordTheRedDog]

mp9,
Thanks for the response.
if you mean like this! yes i have tried it and it doesn't change the results.

SELECT DISTINCTROW  [Location Table].[Product Number], [Location Table].[Part Number], [Location Table].Qty
FROM [Location Table]
WHERE ((([Location Table].[Product Number])=[forms]![CSSimultaneousPanels]![combo1] Or ([Location Table].[Product Number])=[forms]![CSSimultaneousPanels]![combo2]))
ORDER BY [Location Table].[Part Number];

 

[CliffordTheRedDog]

PHV,
Thanks, No dice I'm afraid

SELECT DISTINCT [Product Number], [Part Number], .Qty
FROM [Location Table]
WHERE [Product Number]=[Forms]![CSSimultaneousPanels]![combo1] OR [Product Number]=[Forms]![CSSimultaneousPanels]![combo2]
ORDER BY [Part Number];

 

[traingamer]

When you say 'parts that are unique', do you mean

All of the parts that are in these two products? (Union)
or
All of the parts that appear in ONE or the OTHER of these products but not in both?
or
All of the parts that are in BOTH products? (intersection)
or
something else?

traingamer

 

[traingamer]

the third part should read
ONLY the parts that are in BOTH products? (intersection)

traingamer

 

[CliffordTheRedDog]

traingamer
Thanks,

All of the parts that appear in ONE or the OTHER of these products but not in both?

Yes that is it exactly,

is this I suspect from your question - different to Unique?

Cliff

 

[PHV]

but not in both
SELECT A.[Product Number], A.[Part Number], A.Qty
FROM [Location Table] A
WHERE (A.[Product Number]=[Forms]![CSSimultaneousPanels]![combo1] OR A.[Product Number]=[Forms]![CSSimultaneousPanels]![combo2])
AND 1=(SELECT Count(*) FROM [Location Table] B WHERE (B.[Product Number]=[Forms]![CSSimultaneousPanels]![combo1] OR B.[Product Number]=[Forms]![CSSimultaneousPanels]![combo2]) AND B.[Part Number]=A.[Part Number])

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

 

[traingamer]

Should that be

...AND 1[COLOR=red]<>[/color]...

rather than ...AND 1=...?

Or am I misinterpreting it?

traingamer

 

[CliffordTheRedDog]

phv,

That is spot on thankyou, I will now work out the code, but it is nothing like i have seen before!
Thanks again.
Cliff

 

[traingamer]

Duh, I finally get it.
Well done PH.

traingamer