Simple Query or not?

[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?

 

[Marshall Barton]

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

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

 

[Barry]

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?


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

 

[giorgio rancati]

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.

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

 

[Marshall Barton]

Good catch giorgio.
--
Marsh
MVP [MS Access]

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)

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.

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

 

[Barry]

Yep, that did it!

Thanks guys.

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.

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