B
Barry
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?
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?