Query help

  • Thread starter Thread starter JP
  • Start date Start date
J

JP

I have data similar to below. I need to return entries that have multiple
countries of origin for the same product ID and description. Product 111
below is sourced from two countries and I need to return the two line
pertaining to those lines.

Product ID Prod Desc Country Of Origin
111 lightbulb China
112 paper Germany
111 lightbulb Mexico
113 fork Japan

My Query should look like this:

Product ID Prod Desc Country Of Origin
111 lightbulb China
111 lightbulb Mexico
 
Use these two queries --
qryProductMultiCountry
SELECT [Product ID], Count([Country Of Origin]) AS CountCountry
FROM [YourTable]
WHERE Count([Country Of Origin]) >1
GROUP BY [Product ID];

SELECT [Product ID], [Prod Desc], [Country Of Origin]
FROM [YourTable] LEFT JOIN [qryProductMultiCountry] ON [YourTable].[Product
ID] = [qryProductMultiCountry].[Product ID];
 
If when product id appears more than once it always has a different
country then the following will work for you.

SELECT *
FROM [YourTableName]
WHERE [Product Id] IN
(SELECT [Product Id]
FROM [YourTableName]
GROUP BY [Product id]
Having Count(*) > 1)

You can build the above query using the query wizard's find duplicates
option.

IF it is possible to have the product id and country of origin repeat in
you table then you need to use a more complex query to solve this.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top