How to create report, possibly using 2 queries

[lorirobn]

Hi,

I have a table of all Activity Details for rooms in a building.
The table has
RoomID
ActivityDate
ActivityID
SubActivityID

I need to create a report that will essentially be a culmination of the steps I will list below. I am fairly new to writing a query based on another query, but I think this is what I need to do. I know the logical steps I need to follow to get my final info, but how can I do this with Access?

Here are the logical steps:
- (1) select all Activity Records with ActivityID = "T" and SubActivityID = "O".
- (2) for each RoomID in Step 1, determine if there are any records on the same table for that same RoomID, with ActivitySubID = "H" and ActivityDate >= the ActivityDate in Step 1.
- (3) If such a record does exist, ignore this RoomID. If such a record does not exist, print this RoomID on the report.

I just don't know how to do this in Access, although I believe I need maybe 2 queries?

Thanks in advance for any help!

Lori

 

[lorirobn]

After attempting to come up with queries, I realize I need a revision in my steps.

Here is the revised list of logical steps:

- (1) select all Activity Records with ActivityID = "T" and SubActivityID = "O"
- (1B) - give me the LAST SubActivityID = "O" (meaning, the one with the last/highest ActivityDate)
- (2) for each RoomID in Step 1B, determine if there are any records on the same table for that same RoomID, with ActivitySubID = "H" and ActivityDate >= the last ActivityDate in Step 1B.
- (3) If such a record does exist, ignore this RoomID. If such a record does not exist, print this RoomID on the report.

I hope this makes sense!

 

[lorirobn]

Hi - I did it in 4 queries...but got my results...
Thanks anyway!