How do I return record with earliest date? (2 Viewers)

I've googled this and found several explanations on how to do this. But none of them seem to deal with the case where there are multiple fields.

Example:

I have a table with a dozen columns. One of them is a date field. One of them is a transaction ID.

I want to return a record for each transaction ID with the earliest date field.

Logically, I would think I would do "group by" on the transaction ID then "min" on the date field.

This doesn't work, and I imagine it's because when I turn on the group by all of the fields have "group by" applied to them.

I really don't understand how the group by feature works in access design view.

I want the first record for each transaction ID. first meaning first date in the date field.

you have to select only those field you want to group. if you want more records in the end result, use the group by query to select the other fields.

I'm sorry that made no sense to me at all.

I have a bunch of fields. Two of which are transactionID and transactionDate

I want to return one record for every unique transactionID and that record be selected by being the one with the earliest transactionDate

In Access query design view, I turned on the group by functionality.

It immediately applied Group By to ALL fields. I then selected Min for the date field and the results I got were garbage since I had many with the same transactionID.

I then went into SQL view and tried to just put GROUP BY transactionID at the end and in the first part of the select statement, i changed transactionDate to Min(transactionDate) AS minOfTransactionDate

These changes to the sql equated to making all the other fields have a group by option of "expression" which stops the query from running at all. It gives some error about the function not existing or whatever.

This requires two. Queries, what is happening to you is that the query is grouping on all fields and therefore the differences in those fields, so
Query1 just select transactionid and date fields group by transactionid and min on date field.
This gives the min date for each transactionid so now quer u2 takes in the table and query1 joined on transactionid and date fields and you can select any other data that you require.
Note that you just run query2 the system automatically runs query1

Brian

Here's a visual if it helps:

http://www.baldyweb.com/LastValue.htm

They say a pictures worth a thousand words and sure makes it clear, infact I think a bald guy with penchant for lollipops sang a song about that , not related I suppose

Brian

Thanks for the help. It's clear now.

pbaldy said:

Here's a visual if it helps:

http://www.baldyweb.com/LastValue.htm

Click to expand...

Yes, that's what i meant.

create a query to get the records you want. because of the group by clause you can't get all the fields that you want to see.
so, that's why two queries are needed to produce the results you want to see.

Yes, you can put it all in one query but that query will be less readable and less maintainable.

enjoy!