Avatar of ammiewinds
ammiewindsFlag for United States of America asked on

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)

FIELD EXAMPLES:
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

RESULT NEEDED:
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.
Microsoft Access

Avatar of undefined
Last Comment
ammiewinds

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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
ASKER
ammiewinds

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.
ASKER
ammiewinds

oh... Plus this needs to be a make table so I can do  another query based off of it.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ammiewinds

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.
ASKER
ammiewinds

Hello Capricorn,

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

Sincerely,

Ammiewinds