Trouble with Totals Queries Access 2010

[dawnd3]

I often have trouble with totals queries. So I start with just one field at a time to make sure I am doing it right. I have a data field (say company name) I am grouping by, and then a date field. I want to see the records with the most recent date. So I group by company and sort descending by date and choose LAST as the grouping option. I do not sort the company name field. I run the query, and it shows me a random date. (not the most recent like it should, but not the most past.) I try different sorts and get blanks or other dates. I would think just using 2 fields I would get this right, but I cannot figure out what I am doing wrong. I check and double check the data so I know what to expect in my results, and it is just not happening. Any ideas? Just seems buggy but I am smart enough to know it is probably user error. LOL

Dawn

 

[dawnd3]

Here is the SQL of the query I am doing if it helps.

SELECT tblExhibitors.strEmailDomain, Last(tblExhibitors.dtmDateLastUpdated) AS LastOfdtmDateLastUpdated, Last(tblExhibitors.strFirstName) AS LastOfstrFirstName, Last(tblExhibitors.strLastName) AS LastOfstrLastName, Last(tblExhibitors.strEmail) AS LastOfstrEmail
FROM tblExhibitors
GROUP BY tblExhibitors.strEmailDomain
ORDER BY Last(tblExhibitors.dtmDateLastUpdated) DESC;

If I pull up a specific email domain, ungrouped, I see that 5/24/2013 is the most recent record for that domain. But when I group it, a record with 3/15/2013 shows up as the most recent. There are 2012 dates too, so 3/15/2013 is in between the highest and lowest date.

Please help, this is making me crazy.

 

[dawnd3]

Maybe I figured it out. Should I be using MAX for dates instead of first/last?

 

[dawnd3]

Ok, when I use Max, it gives me the right date but doesn't give me the correct name that goes with that record. UGH! It still gives me the 3/15/2013 name, even though it is showing 5/24/2013. Shouldn't it keep all of one record together?

 

[dhookom]

First and Last rarely have any value in totals queries. Min and Max are typically more functional.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT E.strEmailDomain, E.dtmDateLastUpdated, E.strFirstName, E.strLastName, E.strEmail
FROM tblExhibitors E INNER JOIN (
SELECT strEmailDomain, Max(dtmDateLastUpdated) AS LastDate FROM tblExhibitors GROUP BY strEmailDomain
) L OH E.strEmailDomain=L.strEmailDomain AND E.dtmDateLastUpdated=L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dawnd3]

I got an error when I tried your code PHV. However, I got it to work. Not sure if this was the long way around or the actual fix. I first created a query that found the MAX date for each domain. Then I created another query that found the person that properly matched with that date/domain. I am getting the results I want now, so I am happy, but glad to hear if there is a simpler way.

 

[PHV]

I got an error when I tried your code PHV
Which error ? Where ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886