Max Date

Hello Experts,

I am trying to use the Max option on a query to retrieve the following information, yet it continues to bring in both dates.

          Field: Date
          Table: HPNA Count
          Total: Max (I tried "Last" too)

Date                                     Name                          Count
9/3/2013 7:30:13 AM            NJ05214A_NJ_R                          1
8/26/2013 7:13:23 AM          NJ05214A_NJ_R                          2
8/7/2012 6:15:31 PM            NY03212B_NY_R                   5
1/3/2013 8:21:16 PM            NY03212B_NY_R                   3

Date                                     Name                          Count
9/3/2013 7:30:13 AM            NJ05214A_NJ_R                          1
1/3/2013 8:21:16 PM            NY03212B_NY_R                   3

Appreciate your help with this.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
copy the sql that i posted above and save it as qryMaxDate

then create a make table query using the qryMaxDate as the source.
Rey Obrero (Capricorn1)Commented:
try this query

select t.*
from [HPNA Count] as t
inner join
(select max(t2.[date]) as maxdate, t2.[name]
 from [HPNA Count] as t2
 group by t2.[name] ) as t3
on t.[name]=t3.[name] and t.[date]=t3.maxdate
ammiewindsAuthor Commented:
That worked but... (SO don't like to say but.)  I'm affraid that I need to give you more information and with its sensitive nature I am unable to share it.  Super frustrating.  I am not very good with sql and would like to do this in design view, for a better understanding.

If I use the same table and do an inner join on the Date and Name, then set the Total of the Date field to Max, shouldn't this give me the same thing?

HPNA Count                        HPNA Count 1
Date                                       Date
Name                                    Name  
Count                                    Count  

Or is there some kind of expreesion I could build within the Date field to give me the results?

I appreciate your patience.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

ammiewindsAuthor Commented:
oh... Plus this needs to be a make table so I can do  another query based off of it.
ammiewindsAuthor Commented:
That works.  I will need to analyze the data in the morning to make sure this is correct I will submit another message to let cha know how it goes.  

Much appreciated.
ammiewindsAuthor Commented:
Hello Capricorn,

I used your solution and the data is good.  I appreciate your help, once again.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.