Finding The Earliest Date

[Guest]

How can I run a query that only outputs the first (earliest) date of
purchase? I have a table with ID#, Purchase Date, Product ID, and a few other
fields. Each purchase is a new line in the table so one so there could be 20
lines with the same ID# but different purchase dates. I would like a query
that shows the ID# and their first purchase date.

I am running Access 97 and Purchase date is a Date field. I have tried MIN
function and it works when that is the only field populated in the query but
when I try to add ID# it gives e the following error:

“You tried to execute a query that doesn't include the specified expression
as part of an aggregate function or grouping.
Possible cause:

• You didn't enter an aggregate function in the TRANSFORM statement.â€

 

[Marshall Barton]

How can I run a query that only outputs the first (earliest) date of
purchase? I have a table with ID#, Purchase Date, Product ID, and a few other
fields. Each purchase is a new line in the table so one so there could be 20
lines with the same ID# but different purchase dates. I would like a query
that shows the ID# and their first purchase date.

I am running Access 97 and Purchase date is a Date field. I have tried MIN
function and it works when that is the only field populated in the query but
when I try to add ID# it gives e the following error:

“You tried to execute a query that doesn't include the specified expression
as part of an aggregate function or grouping.
Possible cause:

• You didn't enter an aggregate function in the TRANSFORM statement.”

That message makes sense when you stop and think about it.
If you want the ID and the Minimum date related to the ID,
then you have to Group By the ID. Since you're apparently
using a crosstab query, it would help if you would post a
Copy/Paste of the query's SQL statement.

Maybe you don't need a crosstab and just need to see the
earliest date for each ID. In this case, the query would be
a totals type query:
SELECT ID, Min(Purdate) As MinDate
FROM table
GROUP BY ID

BUT,if you are trying to determine the ID of the record with
the earliest date, then you need a different kind of query.
Possibly something like:
SELECT ID, Purdate
FROM table
WHERE Purdate = (SELECT Min(T.Purdate) FROM table As T)
However, that then raises the question of what you want to
do when the are multiple orders with the same earliest
purchase date?