Last of Date Query

[Scottie]

Group,

I have the following query that I have tried using both the LAST and MAX
and yet I still cannot get it to return only the last record determined by
the date.

SELECT room_changes.RID, sys_rooms.[Room#],
Last(room_changes.RoomChangeDate) AS LastOfRoomChangeDate, sys_rooms.ID
FROM sys_rooms INNER JOIN room_changes ON sys_rooms.ID =
room_changes.[NewRoom#]
GROUP BY room_changes.RID, sys_rooms.[Room#], sys_rooms.ID;

it still returns all the room change records....I only want the last one for
each RID

 

[Jeanette Cunningham]

Scottie
create a new query with Room# and RoomChangeDate
right click in one of the query columns in design view and choose totals
under Room# choose GroupBy
under RoomChangeDate choose Max

switch to datasheet view and see if this is close to what you want.

Jeanette Cunningham

 

[John Spencer]

SELECT room_changes.RID
, sys_rooms.[Room#]
, MAX(room_changes.RoomChangeDate) AS LastOfRoomChangeDate
, sys_rooms.ID
FROM sys_rooms INNER JOIN room_changes
ON sys_rooms.ID = room_changes.[NewRoom#]
GROUP BY room_changes.RID
, sys_rooms.[Room#]
, sys_rooms.ID;

You can do this in TWO queries or use a correlated subquery in the where
clause. The correlated subquery solution would probably look something
like the following.

SELECT room_changes.RID
, sys_rooms.[Room#]
, room_changes.RoomChangeDate AS LastOfRoomChangeDate
, sys_rooms.ID
FROM sys_rooms INNER JOIN room_changes
ON sys_rooms.ID = room_changes.[NewRoom#]
WHERE room_changes.RoomChangeDate =
(SELECT Max(tmp.RoomChangeDate)
FROM room_Changes as Tmp
WHERE tmp.[NewRoom#] = Room_Changes.[NewRoom#])


Two query solution:
qLastChange
SELECT Max(RoomChangeDate) as LastDate
, NewRoom# as NewRoom
, RID
FROM room_Changes
GROUP BY [NewRoom#], RID

Now use that query and your original sys_rooms table

SELECT room_changes.RID
, sys_rooms.[Room#]
, LastDate
, sys_rooms.ID
FROM sys_rooms INNER JOIN qLastChange
ON sys_rooms.ID = qLastChange.NewRoom

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Scottie]

Another Great reply thank you.....

SELECT room_changes.RID
, sys_rooms.[Room#]
, MAX(room_changes.RoomChangeDate) AS LastOfRoomChangeDate
, sys_rooms.ID
FROM sys_rooms INNER JOIN room_changes
ON sys_rooms.ID = room_changes.[NewRoom#]
GROUP BY room_changes.RID
, sys_rooms.[Room#]
, sys_rooms.ID;

You can do this in TWO queries or use a correlated subquery in the where
clause. The correlated subquery solution would probably look something
like the following.

SELECT room_changes.RID
, sys_rooms.[Room#]
, room_changes.RoomChangeDate AS LastOfRoomChangeDate
, sys_rooms.ID
FROM sys_rooms INNER JOIN room_changes
ON sys_rooms.ID = room_changes.[NewRoom#]
WHERE room_changes.RoomChangeDate =
(SELECT Max(tmp.RoomChangeDate)
FROM room_Changes as Tmp
WHERE tmp.[NewRoom#] = Room_Changes.[NewRoom#])


Two query solution:
qLastChange
SELECT Max(RoomChangeDate) as LastDate
, NewRoom# as NewRoom
, RID
FROM room_Changes
GROUP BY [NewRoom#], RID

Now use that query and your original sys_rooms table

SELECT room_changes.RID
, sys_rooms.[Room#]
, LastDate
, sys_rooms.ID
FROM sys_rooms INNER JOIN qLastChange
ON sys_rooms.ID = qLastChange.NewRoom

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Group,

I have the following query that I have tried using both the LAST and MAX
and yet I still cannot get it to return only the last record determined by
the date.

SELECT room_changes.RID, sys_rooms.[Room#],
Last(room_changes.RoomChangeDate) AS LastOfRoomChangeDate, sys_rooms.ID
FROM sys_rooms INNER JOIN room_changes ON sys_rooms.ID =
room_changes.[NewRoom#]
GROUP BY room_changes.RID, sys_rooms.[Room#], sys_rooms.ID;

it still returns all the room change records....I only want the last one for
each RID