A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to add another field to a max query result without grouping



 
 
Thread Tools Display Modes
  #1  
Old December 30th, 2009, 06:11 PM posted to microsoft.public.access.queries
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default How to add another field to a max query result without grouping

I am trying to create a query to find the most recent locationID for each
item with a TrkID

but i can only seem to get it with two queries then join it back on lochist
date and TRKiD Is this the best way??
Here is the query to get the max date for each TRKID isnt there a clean way
to add in one more field "LocationID" ??

SELECT TRKID, Max(T_LocationHistory.LocHistDate) AS MaxOfLocHistDate
FROM T_LocationHistory
GROUP BY TRKID;

if i do i get the max date for each unique TrkID and LocationID combo

Thanks

  #2  
Old December 30th, 2009, 06:29 PM posted to microsoft.public.access.queries
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default How to add another field to a max query result without grouping

Why does it seem you cant find the answer till you post a question??
Looked harder and got it with this

SELECT T_LocationHistory.TRKID, T_LocationHistory.LocationID,
T_LocationHistory.LocHistDate
FROM T_LocationHistory INNER JOIN
[SELECT T_LocationHistory.TRKID, Max(T_LocationHistory.LocHistDate) AS
MaxOfLocHistDate
FROM T_LocationHistory
GROUP BY T_LocationHistory.TRKID]. as Q
ON T_LocationHistory.Lochistdate = Q.MaxOfLocHistDate and
T_LocationHistory.trkid = Q.trkid;

Thank You John Spencer
"Barry A&P" wrote:

I am trying to create a query to find the most recent locationID for each
item with a TrkID

but i can only seem to get it with two queries then join it back on lochist
date and TRKiD Is this the best way??
Here is the query to get the max date for each TRKID isnt there a clean way
to add in one more field "LocationID" ??

SELECT TRKID, Max(T_LocationHistory.LocHistDate) AS MaxOfLocHistDate
FROM T_LocationHistory
GROUP BY TRKID;

if i do i get the max date for each unique TrkID and LocationID combo

Thanks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:51 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.