Selecting the last record

[Melanie]

I am working in Access97.

I have a table that has two fields: Parent Name and child's birthdate.

There are multiple records for each name because the parents could have more than one children. I want collapse the data so there is only one record per name and the youngest child's birthdate (aka largest date). Is there a way in the query to group by name and select the last date. When I try and do that, I don't always get the latest date. It does work however, if I first make a table of the data and then do the group by query.

I would much rather avoid having to do the intermediary step of making a table.

Thanks in advance.

 

[bat17]

It should do it by having a query with just those two fields in the grid with GroupBy on the name and Max for the date.

If that doesnt work can you post the SQL of what you are trying so that we can take a look at it.

peter

 

[Melanie]

Thank you.

One other similar dilemma: I have a table where I would like to group by field A and B and take the last record from field C. However, when I try using the last feature, I am not always getting the last record.

 

[bat17]

Same rules apply
Just the Fields you need
GroupBy on A and B
Max on C

HTH

Peter

 

[Melanie]

But I don't necessarily want the largest. I simply want the last record in the list. For example I am working with a transactional level database and I want the item in column C associated with the last transaction that was done. Even if I sort by columns A (name) and B (date), I do not necessarily get the last item associated with the latest date.

 

[bat17]

try putting B in twice, once with group and again with Max. you will probably need to group on C as well

Peter