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.
ammiewindsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
0
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.
0
ammiewindsAuthor Commented:
oh... Plus this needs to be a make table so I can do  another query based off of it.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
ammiewindsAuthor Commented:
Hello Capricorn,

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

Sincerely,

Ammiewinds
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.