Selecting or displaying only the top value from a group.

[binbin]

Hi everyone,

I have a table with the following sample values. The Sequenceencoded field is not in the table, but just a reference on which one was last or first encoded.

Name Date Amount (SequenceEncoded)
A 4/13/2014 20 2
A 4/12/2014 15 1
A 4/08/2014 5 3

With the value above, I only wanted to get the most recent amount which would be A - 4/13/2014 - 20. So I made a totals query grouping the Name field and then used max on the Date field to get the most recent date. Now as expected, I still get 3 rows since I can't group the last field which is the Amount field. Now I've read from some articles that say just to choose the "First" function from the Total combo list (Group by, min, sum, etc) to get the first value in a row.

But to use that in my query, that would give me the value A - 4/13/2014 - 15, the Name and Date fields will be correct, but the value from the Amount field will be wrong, this is because as I found out, that "First" corresponds to literally the first data that was encoded in that field, which according to the imaginary sequence is the value "15." If I use "Last" this would give me the value "5" since it was the last one encoded. If possible, I would only like to get the first row from each group.

Is there any other way to do this using access queries? I'm not familiar with SQL code so I'm trying to avoid that, but if needed, I'm willing to give it a try....



Thank you for your time.

[pbaldy]

Does this help?

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

[binbin]

Hi pbaldy, thank you for replying.

I tried doing it in SQL, but can't seem to make it to work, encountering error "character found after end statement" which I'm sure it's because of the way I constructed it. So I re-read some of the suggested solutions I found on the net which was to make 2 queries and then joining them to get the values, however, when I do this, the query I made always gave me all the records in the group.

But after I saw your code, I noticed that the joins where between the maxdate and the date on the other table, this was new to me since I always join a primary key to another table or query but never between 2 non-keys. So I tried the suggested solutions again of making 2 queries, one to show the maxdate and the other one included the maxdate query and the original table, then joined the maxdate and date and chose the Amount field... wow it worked!

Thank you so much for the code, I found out why my queries were not working before.

[pbaldy]

Happy to help!