how to combine two queries?

[smr]

Table format:
item_nbr (int)
date (date)
status (memo)

Sample data:
10 1/15/2004 "some text1"
10 1/31/2004 "some text2"
20 1/1/2004 "some text3"
20 1/10/2004 "some text4"

I want to select the row with the largest date for each item_nbr. Based on
the sample data above, I want to select the following:
10 1/31/2004 "some text2"
20 1/10/2004 "some text4"


Currently I am executing two queries to accomplish the selection, I would
like to be able to select using one query.

SELECT max(date) AS max_date, item_nbr INTO temp_table
FROM original_table
GROUP BY item_nbr;

then

SELECT original_table.item_nbr, original_table.date, original_table.status
FROM original_table, temp_table
WHERE original_table.date=temp_table.date AND original_table.item_nbr =
temp_table.item_nbr;

This seems like it should be fairly simple to combine the queries but I
cannot figure out how.

 

[Douglas J. Steele]

Don't both with the temp table. Rewrite your first query as

SELECT max(date) AS max_date, item_nbr
FROM original_table
GROUP BY item_nbr;

and save the query (call it qryMaxDate for the sake of argument)

Create a second query that joins your table to your query:

SELECT original_table.item_nbr, original_table.date, original_table.status
FROM original_table
INNER JOIN qryMaxDate
ON original_table.date=qryMaxDate.max_date
AND original_table.item_nbr = qryMaxDate.item_nbr;

Now, all you need to do is run this second query: the first one will get run
automatically.

By the way, rename your Date field: Date is a reserved word, and you can run
into all sorts of problems using it.