 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / September 2005Simple Query or not?
ID SDate TenantID State Parm Notes 1322 19/09/2005 13:52:29 MRC Raise T1 0 Man: £2200,L141 1323 19/09/2005 13:52:36 MRC T1 Sent 0 Man: £2200,L141 1324 19/09/2005 13:52:44 MRC L1 Sent 0 Man: £4400,L262 1329 19/09/2005 13:57:06 MRC2 Raise T1 0 Man: £6600,L384 1330 19/09/2005 13:57:28 MRC Raise T3 0 Man: £6600,L384 1331 19/09/2005 13:58:09 MRC2 Raise T2 0 Man: £8800,L506 1332 19/09/2005 13:58:22 MRC Raise T2 0 Man: £8800,L506 1333 19/09/2005 14:00:53 MRC2 Raise T1 0 Man: £11000,L627 1334 19/09/2005 14:01:29 MRC Raise T3 0 Man: £11000,L627 I'm trying to get a query that returns the Maximum of each type of TenantID from this table. I've tried Max, Last on the ID and SDate without success. Variants being: Criterion: ID = Max([ID]) Criterion: SDate = Max([SDate]) Within: SELECT DISTINCTROW Max(ArrearsStatus.ID) AS MaxOfID, ArrearsStatus.SDate, ArrearsStatus.TenantID, ArrearsStatus.State, ArrearsStatus.Notes, ArrearsStatus.Parm FROM ArrearsStatus GROUP BY ArrearsStatus.SDate, ArrearsStatus.TenantID, ArrearsStatus.State, ArrearsStatus.Notes, ArrearsStatus.Parm ORDER BY ArrearsStatus.SDate, ArrearsStatus.TenantID; So I want these records returned: ID SDate TenantID State Parm Notes 1333 19/09/2005 14:00:53 MRC2 Raise T1 0 Man: £11000,L627 1334 19/09/2005 14:01:29 MRC Raise T3 0 Man: £11000,L627 Where am I going wrong folks? > ID SDate TenantID State Parm Notes > 1322 19/09/2005 13:52:29 MRC Raise T1 0 Man: £2200,L141 [quoted text clipped - 23 lines] > 1333 19/09/2005 14:00:53 MRC2 Raise T1 0 Man: £11000,L627 > 1334 19/09/2005 14:01:29 MRC Raise T3 0 Man: £11000,L627 A group by query wont find specific records, it will only aggregate like values. Try this kind of thing: SELECT A.ID, A.SDate, A.TenantID, A.State, A.Notes, A.Parm FROM ArrearsStatus As A WHERE A.SDate = (SELECT Max(M.SDate) FROM ArrearsStatus As M WHERE M.ID = A.ID) ORDER BY A.SDate, A.TenantID  SignatureMarsh MVP [MS Access] Tried it but doesn't seem to do it. This was the direction I was going in, but it just seems to return all items like my original query. Any more suggestions? > > ID SDate TenantID State Parm Notes > > 1322 19/09/2005 13:52:29 MRC Raise T1 0 Man: £2200,L141 [quoted text clipped - 8 lines] > > > >I'm trying to get a query that returns the Maximum of each type of TenantID from this table. I've tried Max, Last on the ID and SDate without success. Variants being: > >Criterion: ID = Max([ID]) > >Criterion: SDate = Max([SDate]) > > > >Within: > > > >SELECT DISTINCTROW Max(ArrearsStatus.ID) AS MaxOfID, ArrearsStatus.SDate, ArrearsStatus.TenantID, ArrearsStatus.State, ArrearsStatus.Notes, ArrearsStatus.Parm > >FROM ArrearsStatus > >GROUP BY ArrearsStatus.SDate, ArrearsStatus.TenantID, ArrearsStatus.State, ArrearsStatus.Notes, ArrearsStatus.Parm [quoted text clipped - 16 lines] > WHERE M.ID = A.ID) > ORDER BY A.SDate, A.TenantID Hi Barry, you do a small correction to the query ---- SELECT A.ID, A.SDate, A.TenantID, A.State, A.Notes, A.Parm FROM ArrearsStatus As A WHERE A.SDate = (SELECT Max(M.SDate) FROM ArrearsStatus As M WHERE M.TenantID = A.TenantID) ---- Bye Giorgio > Tried it but doesn't seem to do it. This was the direction I was going in, > but it just seems to return all items like my original query. > > Any more suggestions? [CUT] > > A group by query wont find specific records, it will only > > aggregate like values. [quoted text clipped - 7 lines] > > WHERE M.ID = A.ID) > > ORDER BY A.SDate, A.TenantID Good catch giorgio. SignatureMarsh MVP [MS Access] >you do a small correction to the query >---- [quoted text clipped - 22 lines] >> > WHERE M.ID = A.ID) >> > ORDER BY A.SDate, A.TenantID Yep, that did it! Thanks guys. > Hi Barry, > [quoted text clipped - 26 lines] > > > WHERE M.ID = A.ID) > > > ORDER BY A.SDate, A.TenantID |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.