Probably a simple question

[Shon]

I am new to SQL Query so there is probably an easy answer to this question.

I posted a question the other day which gave me this query;

SELECT Order.Order Code, MIN(Order.Order Detail) as AA
FROM [Order]
GROUP BY Order.Order Code

How can I add in other fields from my Order table to display these in the
query aswell e.g. this query shows me the order code for all AA Order details
but I also want to display the cost that relates to this order and the
description. these are all fields held in the order table for the relating
record.

 

[Krzysztof Naworyta]

Shon wrote:
| I am new to SQL Query so there is probably an easy answer to this
| question.
|
| I posted a question the other day which gave me this query;
|
| SELECT Order.Order Code, MIN(Order.Order Detail) as AA
| FROM [Order]
| GROUP BY Order.Order Code
|
| How can I add in other fields from my Order table to display these in
| the query aswell e.g. this query shows me the order code for all AA
| Order details but I also want to display the cost that relates to
| this order and the description. these are all fields held in the
| order table for the relating record.


http://www.mvps.org/access/queries/qry0020.htm

KN

 

[John Spencer]

Next time copy the exact SQL you are using by opening the query in SQL view
and copying the SQL statement.


You can do this using a correlated sub-query.
SELECT [Order].*
FROM [Order]
WHERE [Order].[Order Detail]=
(SELECT Min([Order Detail]
FROM [Order] as TEMP
WHERE Temp.[Order Code]=[Order].[Order Code])

Or you can use the following solution. The solution above returns records
that can be edited. The next solution may be faster (especially with large
numbers of records) but the records returned cannot be edited.

Since your field names have spaces in them you need to use two queries.

First query is the one you are using now.
SELECT Order.[Order Code], MIN(Order.[Order Detail]) as AA
FROM [Order]
GROUP BY Order.[Order Code]

Second query uses that query and your table
SELECT [Order].*
FROM [Order] INNER JOIN [TheSavedQuery]
ON [Order].[Order Code] = [TheSavedQuery].[Order Code]
AND [Order].[Order Detail]=[TheSavedQuery].AA
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Try create another query based on the Order table and your existing query.
Join the appropriate fields.

BTW: I have trouble getting my head around the spaces in your field names
and no []s. You should try to post actual SQL that is copied and pasted into
messages. If not, at least type in the [] so it doesn't mess with my head ;-)