adding like data 1

[jamesgeorge]

I'm doing a nursing data base, and working on lengths of stay, for patients that have been admitted several different times for variable lengths of stay.
So I have a query and the medical record number, first and last name, and diagnosis are all the same and the dates of admission and discharge are different. I simply want to add the totals of the same patients different stays in a top ten patient list.
Thanks in advance, James

 

[JerryDennison]

This is done easiest with a Totals query. I'll assume that each record has a check-in date/time and a check-out date/time. You'll only need two fields added to the QBE grid of the query. The primary key for the patient and an expression to calculate the difference between the check-in and check-out fields. Set the Querie's Top Values property(right click the name bar and select properties) to 10. The expression should look something like this:

HoursAdmitted: DateDiff("h", [CheckInDate], [CheckOutDate])

Set the patient id field's Total property to Group By and the HoursAdmitted field's property to Sum. I've set the DateDiff function to return the difference in hours between the check-in and check-out fields. The query will handle totalling all of these for each patient, ordering and limiting the recordset returned to the top 10 patients. You can then use this query in conjunction with other queries, forms or reports.