query help

  • Thread starter Thread starter sJ
  • Start date Start date
S

sJ

My data is similar to below. I need to extract items for each product ID
that has more than one country in a given year. Lines one and two are what
I need as the product ID 1 has two countries for year 2007. Lines 3,4 and 5
do not meet the criteria.

TIA

Product ID Country Year
1 Canada 2007
1 Mexico 2007
1 Canada 2008
2 Japan 2006
2 Japan 2005
 
Try this:

SELECT *
FROM [YourTable] AS T1
WHERE EXISTS
(SELECT [Product ID]
FROM [YourTable] As T2
WHERE T2.[Product ID] = T1.[Product ID]
AND T2.[Year] = T1.[Year]
AND T2.[Country] <> T1[Country]
GROUP BY [Product ID], [Year]
HAVING COUNT(*) > 0);

Ken Sheridan
Stafford, England
 
Shouldn't that be Count(*) > 1?

As well, the field name should be changed from Year to, say, SalesYear. Year
is a reserved word, and should not be used for your own purposes.

For a comprehensive list of names to avoid (as well as a link to a free
utility that will check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Try this:

SELECT *
FROM [YourTable] AS T1
WHERE EXISTS
(SELECT [Product ID]
FROM [YourTable] As T2
WHERE T2.[Product ID] = T1.[Product ID]
AND T2.[Year] = T1.[Year]
AND T2.[Country] <> T1[Country]
GROUP BY [Product ID], [Year]
HAVING COUNT(*) > 0);

Ken Sheridan
Stafford, England

My data is similar to below. I need to extract items for each product ID
that has more than one country in a given year. Lines one and two are
what
I need as the product ID 1 has two countries for year 2007. Lines 3,4
and 5
do not meet the criteria.

TIA

Product ID Country Year
1 Canada 2007
1 Mexico 2007
1 Canada 2008
2 Japan 2006
2 Japan 2005
Click to expand...
Click to expand...
 
Query One (Q1):

SELECT ProductID, [Year]
FROM SomeTable
GROUP By ProductID, [Year]
HAVING Max(Country) <> Min(Country)

Query Two (uses Q1 and your table):
SELECT SomeTable.*
FROM SomeTable INNER JOIN Q1
On SomeTable.ProductID = Q1.ProductID
AND SomeTable.[Year] = Q1.[Year]

Or all in one query if your field and table names consist of only
letters, numbers, and underscore character.

SELECT SomeTable.*
FROM SomeTable INNER JOIN
(SELECT ProductID, SomeTable.Year]
FROM SomeTable
GROUP By ProductID, SomeTable.Year
HAVING Max(Country) <> Min(Country)
) as Q1
On SomeTable.ProductID = Q1.ProductID
AND SomeTable.[Year] = Q1.[Year]

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

No, that would return rows with more than 2 countries per product per
year. The subquery only returns rows if there is at least one country
*other than* the outer query's current row. In fact the HAVING clause
in the subquery is not really necessary.

Agreed about the Year column heading.

Ken Sheridan
Stafford, England

Shouldn't that be Count(*) > 1?

As well, the field name should be changed from Year to, say, SalesYear. Year
is a reserved word, and should not be used for your own purposes.

For a comprehensive list of names to avoid (as well as a link to a free
utility that will check your application for compliance), see what Allen
Browne has athttp://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


Try this:
Click to expand...
SELECT *
FROM [YourTable] AS T1
WHERE EXISTS
(SELECT [Product ID]
FROM [YourTable] As T2
WHERE T2.[Product ID] = T1.[Product ID]
AND T2.[Year] = T1.[Year]
AND T2.[Country] <> T1[Country]
GROUP BY [Product ID], [Year]
HAVING COUNT(*) > 0);
Click to expand...
Ken Sheridan
Stafford, England
Click to expand...
Click to expand...
 
On second thoughts >1 would return rows where there are two rows or
more for the current product/year for any country other than the
current country. All that's really needed is:

SELECT *
FROM [YourTable] AS T1
WHERE EXISTS
(SELECT *
FROM [YourTable] As T2
WHERE T2.[Product ID] = T1.[Product ID]
AND T2.[Year] = T1.[Year)
AND T2.[Country] <> T1[Country];

Ken Sheridan
Stafford, England

Doug:

No, that would return rows with more than 2 countries per product per
year. The subquery only returns rows if there is at least one country
*other than* the outer query's current row. In fact the HAVING clause
in the subquery is not really necessary.

Agreed about the Year column heading.

Ken Sheridan
Stafford, England

Shouldn't that be Count(*) > 1?
Click to expand...
As well, the field name should be changed from Year to, say, SalesYear. Year
is a reserved word, and should not be used for your own purposes.
Click to expand...
For a comprehensive list of names to avoid (as well as a link to a free
utility that will check your application for compliance), see what Allen
Browne has athttp://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
news:9ed7308d-e8cd-42c6-9f5e-ba85ac0bbce6@q14g2000vbn.googlegroups.com...
Click to expand...
Try this:
SELECT *
FROM [YourTable] AS T1
WHERE EXISTS
(SELECT [Product ID]
FROM [YourTable] As T2
WHERE T2.[Product ID] = T1.[Product ID]
AND T2.[Year] = T1.[Year]
AND T2.[Country] <> T1[Country]
GROUP BY [Product ID], [Year]
HAVING COUNT(*) > 0);
Ken Sheridan
Stafford, England
My data is similar to below. I need to extract items for each product ID
that has more than one country in a given year. Lines one and two are
what
I need as the product ID 1 has two countries for year 2007. Lines 3,4
and 5
do not meet the criteria.
TIA
Product ID Country Year
1 Canada 2007
1 Mexico 2007
1 Canada 2008
2 Japan 2006
2 Japan 2005
Click to expand...
Click to expand...
Click to expand...
 
Back
Top