aggregate query, and also update 2nd table

[lorirobn]

Hi,

I have a table with room, sequence number, and date. A room can have multiple records, with seqno increasing sequentially within each room number.
(ex: room 100, seq 1 date 7/01/2006
room 100, seq 2 date 7/05/2006
room 100, seq 3 date 7/11/2006
room 101, seq 1 date 7/02/2006...)

I want to display each room with its highest sequence number, AND the date. I wrote a query that displays the room and seqno only, but don't know how to display the date, which is not part of the Max or Group By (aggregate) portions and I get errors when I try.

What I REALLY want to do, ultimately, is then take the date and update a date field on a second table for this room number. I've just been doing it in baby steps.

My current SQL is:

SELECT tblPestTreatmentDetails.RoomID, Max(tblPestTreatmentDetails.TreatmentSeqNum) AS MaxOfTreatmentSeqNum, tblPestTreatmentDetails.TreatmentDate
FROM tblPestTreatmentDetails
GROUP BY tblPestTreatmentDetails.RoomID
ORDER BY tblPestTreatmentDetails.RoomID;

Would greatly appreciate any help. I think once I get the aggregate query part working, I can figure out the update (altho' I shouldn't be so sure!).

Thanks in advance.

 

[lorirobn]

ps - in my sql statement, it's the 'tblPestTreatmentDetails.TreatmentDate' that causes the query not to work.

 

[rjoubert]

I'm not sure if this is the best way to go about doing this, but you could create two queries. Your first query would group by the Room Number and get the Max of the Sequence Number.

SELECT tblPestTreatmentDetails.RoomID, Max(tblPestTreatmentDetails.TreatmentSeqNum) AS MaxOfTreatmentSeqNum
FROM tblPestTreatmentDetails
GROUP BY tblPestTreatmentDetails.RoomID
ORDER BY tblPestTreatmentDetails.RoomID;

The 2nd query would use the first query and the tblPestTreatmentDetails table, linking them together by Room Number and Sequence Number, and would display the Treatment Date.

SELECT tblPestTreatmentDetails.RoomID, tblPestTreatmentDetails.TreatmentSeqNum, tblPestTreatmentDetails.TreatmentDate
FROM Query1 INNER JOIN tblPestTreatmentDetails ON (Query1.MaxOfTreatmentSeqNum = tblPestTreatmentDetails.TreatmentSeqNum) AND (Query1.RoomID = tblPestTreatmentDetails.RoomID);

 

[lespaul]

SELECT A.RoomID, Max(A.TreatmentSeqNum), A.TreatmentDate
FROM tblPestTreatmentDetails A
GROUP BY A.RoomID, A.TreatmentDate
ORDER BY tblPestTreatmentDetails.RoomID;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lorirobn]

Lespaul, I tried your suggestion, but got an error. What is 'A' - does it have to be defined in some way?

(Thanks for both responses - I will try rjoubert's next).

 

[rjoubert]

Leslie,

That won't work. You would get the max sequence number for each unique RoomID and TreatmentDate combination. So in the example that lorirobn gave above...

room 100, seq 1 date 7/01/2006
room 100, seq 2 date 7/05/2006
room 100, seq 3 date 7/11/2006
room 101, seq 1 date 7/02/2006

your query would return the same 4 records. If I'm not mistaken (please correct if I'm wrong), lorirobn is looking for the treatment date associated with the max sequence number per Room number...something like this...

room 100, seq 3, date 7/11/2006
room 101, seq 1, date 7/02/2006

 

[PHV]

SELECT A.RoomID, A.TreatmentSeqNum, A.TreatmentDate
FROM tblPestTreatmentDetails AS A INNER JOIN (
SELECT RoomID, Max(TreatmentSeqNum) AS MaxOfTreatmentSeqNum
FROM tblPestTreatmentDetails GROUP BY RoomID
) AS M ON A.RoomID = M.RoomID AND A.TreatmentSeqNum = M.MaxOfTreatmentSeqNum
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

Thanks, PHV, that did the trick.

Now, to complicate matters further, how do I add another table, tblPests, to this query. My ultimate goal with this is to update a field on that table with A.TreatmentDate? tblPests also has the primary key of RoomID.

(Btw, what does 'Order By 1' do?)

Many thanks -
Lori

 

[PHV]

what does 'Order By 1' do?
Sort by 1st column