Max Date

ammiewinds
ammiewinds used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

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

Author

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.

Author

Commented:
oh... Plus this needs to be a make table so I can do  another query based off of it.
Ensure you’re charging the right price for your IT

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

Top Expert 2016
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.

Author

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.

Author

Commented:
Hello Capricorn,

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

Sincerely,

Ammiewinds

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial