Sub Total and Overall Totals in a Report

[StevenCV]

Hi,

I am completely new to Access, and have managed to create a database/form for my boss. However, I am struggling with creating the reports that are needed. I also have a problem with calculating hours/minutes.

I will let you know what I need, and I would be very grateful if someone could point me in the right direction.

About My Database
It collects information on our service users. We have care staff that go out and visit these service users, and data is inputted for each visit, including total time on site. This has been being inputted into excel, but it has outgrown this program. Currently, they input time on site in a format of "1 hour 5 mins". Obviuously, this will not be suitable for totalling up the time.

Report that I Need
One that lists each record (with fields "Log Number", "Client Swift Number", "Client Name", "Client Address") and then shows a total number of hours care provided.

Currently, this is collected in 14 different fields ("1) Total Hours", "2) Total Hours", "3) Total Hours", and so on).

However, I may need to change this. I am not confident in the end users ability to input hours/minutes as decimal figures, so would it be best if I created another field next to each hours field for "Total Minutes"?

If so, I would then need the report to 1) Add up the total hours from 14 hours fields for each record. 2) Add up the total minutes from 14 minute fields for each record and 3) Add these together, ensuring that minutes are converted into hours and the total displays total hours and total minutes. The report would ideally do steps 1 and 2 in the background and just display the complete total.



Finally, I would like an overall total that lists the complete total hours and minutes of every record.

Does this make sense? If you need further clarification, please do not hesitate to ask me to make things clearer.

[June7]

If you have users enter DateTimeArrived and DateTimeDeparture, you can then calculate the elapsed time. Why do you have 14 fields for 'Total Hours'? This is not normalized structure.

Otherwise, separate hours and minutes fields would possibly best serve your situation.

Should avoid spaces, special characters, punctuation in names. Better would be TotalHours1.

[StevenCV]

Thank you for your reply.

We do not have users enter Arrival or Departure times. We just don't capture that information. They just input a total time for how long they have been on site at a clients home.

The reason there are 14, is because we can provide care for up to 2 weeks, and they input the total hours for a visit for each day (alongside other details such as the care given, any concerns etc). So we have one row for each day, hence 14 fields to add up.

We need to add up those 14 fields to give a total hours/minutes provided over the whole 14 days (although some of these may be blank, if no care was given on a particular day).

It is not often that this goes over 24 hours, but it can do. I know setting the field types as 'Time Short' and adding those up gives me the correct answer, but only as long as the total under 23:59. I am also aware that I can multiply the answer by 24 to get the right answer if it is above that total. However, I need a formula that will work in both instances, as there is no way of telling which clients will recieve over 24 hours care.

[StevenCV]

Hi,

My problem has now changed, and I thought it best to put this different problem in a new thread, which can be found here: https://www.accessforums.net/showthr...919#post106919

Sorry for any inconvenience.