Hi,
I want to get information from one table onto another table.
Specifically, table 1 has RoomID, SeqNo, and Date. There may be multiple records for a room number, each with a sequence number sequentially incremented by 1.
For example:
Room 100, seqno 1, Date 07/01/2006
Room 100, seqno 2, Date 07/05/2006
Room 100, seqno 3, Date 07/11/2006
Room 200, seqno 1, Date 07/03/2006.
I want to get the highest sequence number for each room. Then I want to take the Date on that record, and populate a date field on Table2 with it.
There are really 2 parts to my question. One is how to do the actual VBA code or query to do the selects and the update. The other question is - how and where do I do this processing? Up 'til now, I have done all my VBA coding in forms and reports. This, however, is a one-shot deal that I want to do to make updating easy for me. Would I create a new module. How would I do so, and how would I run it? (I feel like I'm going back to being a newbie).
As far as the first question of programming the sql, I've been able to write a query to get the highest sequence number, but it doesn't let me display the date. I know also that once I get the date, I want to do processing (I want an if statement to determine which date field the date should go into).
If it hleps, my sql from the query is as follows:
Thanks in advance for any help. I had also posted this question on the Access query forum, when I thought it could be a simple query.
I want to get information from one table onto another table.
Specifically, table 1 has RoomID, SeqNo, and Date. There may be multiple records for a room number, each with a sequence number sequentially incremented by 1.
For example:
Room 100, seqno 1, Date 07/01/2006
Room 100, seqno 2, Date 07/05/2006
Room 100, seqno 3, Date 07/11/2006
Room 200, seqno 1, Date 07/03/2006.
I want to get the highest sequence number for each room. Then I want to take the Date on that record, and populate a date field on Table2 with it.
There are really 2 parts to my question. One is how to do the actual VBA code or query to do the selects and the update. The other question is - how and where do I do this processing? Up 'til now, I have done all my VBA coding in forms and reports. This, however, is a one-shot deal that I want to do to make updating easy for me. Would I create a new module. How would I do so, and how would I run it? (I feel like I'm going back to being a newbie).
As far as the first question of programming the sql, I've been able to write a query to get the highest sequence number, but it doesn't let me display the date. I know also that once I get the date, I want to do processing (I want an if statement to determine which date field the date should go into).
If it hleps, my sql from the query is as follows:
Code:
SELECT tblTreatment.RoomID, Max(tblTreatment.SeqNum) AS MaxSeqNum
FROM tblTreatment
GROUP BY tblTreatment.RoomID
ORDER BY tblTreatment.RoomID;Thanks in advance for any help. I had also posted this question on the Access query forum, when I thought it could be a simple query.
