DISPLAY ONLY IF PART# if is in all three locations.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
Hope I can explain it. Is there a way to ONly select part# If only it meets
the 3 regions.
Example,
PARTNUMBER REGION
XYZ NA
ZZZ EU
ZZYY AP
XYZ EU
TEST AP
XYZ AP

So what I would want is:
XYZ NA
XYZ EU
XYZ AP

Basically if the part number doesn't match all 3 then don't display.

I tried to put in my criteria field under REGION Like "NA*" AND Like "AP*"
And Like "EU*"
ALSO IN ("NA","AP","EU") but these don't give me what I want.

Please advise any info. Would appreciate.

Thanks,

Juan
 
Create two queries
1. A group by query that returns all the Partnumbers that apears three times
SELECT Count(MyTable.PARTNUMBER) AS CountOfPARTNUMBER
FROM MyTable
WHERE MyTable.REGION In ("NA","AP","EU")
HAVING Count(MyTable.PARTNUMBER)=3

2. A query that join the above query with the table by the partnumber, and
display the fields from the table
 
Hello Ofer,
thanks for the quick reply, but this won't do. Becaseu a part number in
lets say NA could appear 3 times. im selecting more fields example RATE, DATE.
So for my example,
PART REGIION RATE DATE
XYZ NA 0.25 05/04/2005
XYZ NA 1.2 08/04/2005
XYZ NA .15 07/10/2005
but maybe part number XYZ doesn't existi in AP and EU.So I don't want this
to be display. Only if this part is also in AP and EU.
Hope this is more clear. and i apologyze for not been more clearer.

Please provide any feedback you can provide.

thanks,
juan
 
You can create another query, to group by on the two fields Part and region,
and then base the second query (the first one in the prev post). that way the
records wont repeat them self.
 
You can try a query with three subqueries.

SELECT PartNumber, Region
FROM YourTable
WHERE PartNumber in
(SELECT T.PartNumber From YourTable as T WHERE Region = "NA")
AND PartNumber in
(SELECT T.PartNumber From YourTable as T WHERE Region = "EU")
AND PartNumber in
(SELECT T.PartNumber From YourTable as T WHERE Region = "AP")
 
Back
Top