Cumulative data in a query where there may be more than one row for each person.

[Futtecr]

While I am not new to databases I am relatively new to Acces (using 2010). In a daughter table which is the "many" of a 1 to many relationship, I want to calculate TOTAL amount of time. There is a start date/time and an end date/time. That part is easy. There may be 1 or more than one record for each patient. I want the total time. So if the first occurance (record) was 2 days and the 2nd occurance (another record) was 1 day the total would be 3 days. In this table there are many patients. Can I do this in a query?
The fields I would use are the caseID, StartDate, StartTime, EndDate, EndTime. (I did not design this table)

Thanks in advance,

Craig

[June7]

Use DateDiff function in query to calculate the elapsed days. Then build a report with that query as RecordSource. Build a group on the patient ID. In group footer textbox, calculate total days with Sum function. Example query:

SELECT *, DateDiff("d",StartDate,EndDa5t) + 1 As Days FROM tablename;

If you have to account for partial days by use of the Start and End times, that gets a bit more complicated, especially with the data and time parts in separate fields.