Update a field in a table after ordering data in two other fields

[majamer]

Hello,

I have a table with one PatientNumber and TestDate fields among others. Each PatientNumber has several records with different TestDate. I added a new field called TestNumber and wanted to sort the table by PatientNumber and TestDate then update the TestNumber field to increment test numbers for each patient by test date. I was able to create the function to increment the numbers for each patient and reset the counter for the next patient, but the problem I'm having is to write an UPDATE SQL statement that does the update after sorting the data by PatientNumber then TestDate. ORDER BY or GROUP BY don't work with UPDATE queries. What's the best way to handle this?

Thanks,

Martha

[Ahmed Elalfy]

Dear Martha,

I think it will be better to craete a new table contains Patients data and add the field TestNumber in this table.
So you can create a summary query on the old table and update the value in TestNumber in the new table.

Thanks

Ahmed