Finding Matching data

  • Thread starter Thread starter Shon
  • Start date Start date
S

Shon

I have the following data;

Order Code Order Detail
100 AA
101 AA
100 BB
102 AA
101 BB

I want to create a query that will look at the order code and if the order
code is duplicated to only show AA order details but if there is only 1 order
code then just to display the order detail for that e.g. If I had the query I
would expect to see the following results;

Order Code Order Detail
100 AA
101 AA
102 AA

Any ideas?
Thanks
 
Based on the data you posted you could use a query that looks like the following:

SELECT [Order Code], Min([Order Detail]) as AAorOther
FROM YourTable
GROUP BY [Order Code]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
How is the query to know to pick 100 AA over 100 BB?
Is there something about AA for the query to make the decission?
 
The order detail will always be AA or BB but the order code may be any
number. Therefore whatever the Order Code is, if there is a duplicate order
code, the order detail AA will always take precedence over any other code. I
created a find duplicates query which partly worked but did not return data
for those codes that did not have duplicate values e.g it gave me; Order Code
100, 101 both with order detail AA but did not show Order Code 102 because
there is only 1 entry for this.

I hope this makes sense?

Shon
 
I have put this in the query but get the following message;

You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main querys FROM clause. Revise the
SELECT statement of the subquery to request only one field.

Any ideas?

John Spencer said:
Based on the data you posted you could use a query that looks like the following:

SELECT [Order Code], Min([Order Detail]) as AAorOther
FROM YourTable
GROUP BY [Order Code]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have the following data;

Order Code Order Detail
100 AA
101 AA
100 BB
102 AA
101 BB

I want to create a query that will look at the order code and if the order
code is duplicated to only show AA order details but if there is only 1 order
code then just to display the order detail for that e.g. If I had the query I
would expect to see the following results;

Order Code Order Detail
100 AA
101 AA
102 AA

Any ideas?
Thanks
Click to expand...
.
Click to expand...
 
That was an entire query. It could not be used as a subquery in the WHERE
clause or in the SELECT clause.

I suspected that you wanted more than you stated. Can you post the SQL of the
query you had that was working? I suspect that you need a query like the
following:

SELECT [SomeTable].*
FROM [SomeTable]
WHERE [Order Detail] =
(SELECT Min[Order Detail]
FROM SomeTable as TEMP
WHERE Temp.[Order Code] = [SomeTable].[Order Code])

Another way to accomplish the result is to use two queries. The first query
would be the one I originally posted. Save it as a query.

SELECT [Order Code], Min([Order Detail]) as AAorOther
FROM YourTable
GROUP BY [Order Code]

Now use that saved query as an additional table in your current query and join
the Order Code and AAorOther fields to the Order Code and Order Details fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have put this in the query but get the following message;

You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main querys FROM clause. Revise the
SELECT statement of the subquery to request only one field.

Any ideas?

John Spencer said:
Based on the data you posted you could use a query that looks like the following:

SELECT [Order Code], Min([Order Detail]) as AAorOther
FROM YourTable
GROUP BY [Order Code]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have the following data;

Order Code Order Detail
100 AA
101 AA
100 BB
102 AA
101 BB

I want to create a query that will look at the order code and if the order
code is duplicated to only show AA order details but if there is only 1 order
code then just to display the order detail for that e.g. If I had the query I
would expect to see the following results;

Order Code Order Detail
100 AA
101 AA
102 AA

Any ideas?
Thanks
Click to expand...
.
Click to expand...
Click to expand...
 
That works!

Thanks so much for your help.

Shon

John Spencer said:
That was an entire query. It could not be used as a subquery in the WHERE
clause or in the SELECT clause.

I suspected that you wanted more than you stated. Can you post the SQL of the
query you had that was working? I suspect that you need a query like the
following:

SELECT [SomeTable].*
FROM [SomeTable]
WHERE [Order Detail] =
(SELECT Min[Order Detail]
FROM SomeTable as TEMP
WHERE Temp.[Order Code] = [SomeTable].[Order Code])

Another way to accomplish the result is to use two queries. The first query
would be the one I originally posted. Save it as a query.

SELECT [Order Code], Min([Order Detail]) as AAorOther
FROM YourTable
GROUP BY [Order Code]

Now use that saved query as an additional table in your current query and join
the Order Code and AAorOther fields to the Order Code and Order Details fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have put this in the query but get the following message;

You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main querys FROM clause. Revise the
SELECT statement of the subquery to request only one field.

Any ideas?

John Spencer said:
Based on the data you posted you could use a query that looks like the following:

SELECT [Order Code], Min([Order Detail]) as AAorOther
FROM YourTable
GROUP BY [Order Code]

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

Shon wrote:
I have the following data;

Order Code Order Detail
100 AA
101 AA
100 BB
102 AA
101 BB

I want to create a query that will look at the order code and if the order
code is duplicated to only show AA order details but if there is only 1 order
code then just to display the order detail for that e.g. If I had the query I
would expect to see the following results;

Order Code Order Detail
100 AA
101 AA
102 AA

Any ideas?
Thanks
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top