SUM in Report Footer / Total in Form not working

[STEVENCV]

Hi,

I have a database (attached with dummy data in it), which has two tables.

The first table 'Crisis Calls Attended' is for recording client information such as contact details and details of thier health and the care they need.

Our staff then go out on visits to care for the client, so I have a seperate table called 'Visits', which is linked to the main table via the "Log No" fields.

They enter the date of the visit, plus how many hours and minutes they have been on site.

I have created a form, with the visits form as a subform of it. This fsubform adds up the hours and minutes columns for each client and totals this into an HH:MM format.

However, when I try to create a report that will total all visits for ALL records, I am unable to do so.

I have two queries:

1) Could someone please take a look at the attached database and let me know what I am doing wrong in the report?

2) Can someone explain how come the total in the visits subform (within the main form) does not total up correctly unless I click into one of the fields, upon which the total box magically updates to the correct number?

 

[Severin]

In both circumstances you refer to fields in your calculation which has calculations in them. You need the full calculation in your totals field!~)

 

[STEVENCV]

Thank you for your reply. Is it possible for you to go into more detail with how I should write the formula?

 

[Severin]

If you have 3 textboxes Units, PrizePrUnit and TotalPrize

then TotalPrize = Units * PrizePrUnit

if you have multiple lines and you want a total then you cannot say sum(TotalPrize), you have to say sum(Units * PrizePrUnit)

 

[Severin]

so your total on the report would be:

=IIf((Sum([Visits]![Total Hours Provided])+Sum([Visits]![Total Minutes Provided])\60)<10,"0"+CStr(Sum([Visits]![Total Hours Provided])+Sum([Visits]![Total Minutes Provided])\60),CStr(Sum([Visits]![Total Hours Provided])+Sum([Visits]![Total Minutes Provided])\60))+":"+IIf((Sum([Visits]![Total Minutes Provided]) Mod 60)<10,"0"+FormatNumber(Sum([Visits]![Total Minutes Provided]) Mod 60,0),FormatNumber(Sum([Visits]![Total Minutes Provided]) Mod 60,0))

 

[STEVENCV]

You are a genius, thank you.

 

[STEVENCV]

Sorry to pester, but I have one more query.

If I want the field 'Date of Visit' to show in the report next to it's corresponding Hours Total, what formula would I put in the box?

I have tried:

=Visits![Date of Visit]

but this didn't work.

 

[Severin]

You need to change your recordsource on the report to:

SELECT Visits.[Log No:] AS [Visits_Log No], [Crisis Calls Attended].[Client Name:], [Crisis Calls Attended].[Client Address:], Visits.[Total Hours Provided], Visits.[Total Minutes Provided], [Crisis Calls Attended].[Log No:] AS [Crisis Calls Attended_Log No], Visits.[Date of Visit] FROM [Crisis Calls Attended] INNER JOIN Visits ON [Crisis Calls Attended].[Log No:] = Visits.[Log No:];

..and then you add the date of visit field to your report and then you need to redo your grouping and sorting on the report.

...by the way before you get to far then I would strongly suggest you rename your fields in your tables... fx. [Deterioration of Client's Carer's Health:] to DeteriorationOfClientsCarersHealth

Otherwise you will have problems with querying and if you do any vba. It was already playing up when I went in to the recordsource. It couldn't actually run and it's also the reason why you have to redo the grouping and sorting!~)

 

[STEVENCV]

Also, how do I solve the issue of my total in the Visits Form not adding up correctly until I physically click into a field?

 

[STEVENCV]

Thank you for your help. So I should rename every field so that each name has no spaces and/or punctuation?

I will attempt to follow your instructions, and then re-upload the database for you to look it, if you don't mind continuing to help me.

 

[Severin]

total in the visits form is the same issue as total on the report, so it would be:

=IIf((Sum([Total Hours Provided])+Sum([Total Minutes Provided])\60)<10,"0"+CStr(Sum([Total Hours Provided])+Sum([Total Minutes Provided])\60),CStr(Sum([Total Hours Provided])+Sum([Total Minutes Provided])\60))+":"+IIf((Sum([Total Minutes Provided]) Mod 60)<10,"0"+FormatNumber(Sum([Total Minutes Provided]) Mod 60,0),FormatNumber(Sum([Total Minutes Provided]) Mod 60,0))

 

[STEVENCV]

Hi,

I have uploaded the latest version of my database.

I renamed all of the fields so that there are no spaces and no punctuation.
I changed the formula in the Visits Form to the one above (with the new "no spaces" field names in it.

I then changed the record source of my report as you instructed, but I think I have made a mistake somewhere with the renaming of fields, because when I go to report view now, I get input boxes asking me for the parameters of:

Crisis Call Attended_Log No:
and
Visits_Log No:

and I don't know how to remove these.

I also have a couple more queries, if you don't mind.

1) How do I reorder the report based on Log No, ascending instead of decensing?

2) Is it possible to have a sub total after each person's records in the report? I.e. It shows all the visit records for Log No 1, and shows a sub total for those visits, and then it shows all the visit records for Log No 2, and then sub totals these? This would be a subtotal per log no, not a continuour/rolling sub total.

3) Is it possible to have second, basic report, that just shows the subtotal of each log no, without listing each individual visit? And then total that at the bottom? That way, I can have a 'Detailed Report' and a 'Basic Report'.

This would make it much better as there is likely to be a huge number of visits, and the basic report would probably be used more often than the detailed one.

I really do appreciate your help on this. As you can see, I am new Access and struggling to find my way in getting it to do what I want it to.

 

[Severin]

The parameters and question 1 are solved by right clicking an select sorting and grouping. It will show 'expression' in group by and in sort by, change that in the dropdown.

2) When you look at the grouping you can add a footer and a sum field here will give you subtotals.

3) Yes, in teory you change your recordsource to:

SELECT [Crisis Calls Attended].ClientName, [Crisis Calls Attended].ClientAddress, Sum(Visits.TotalHoursProvided) AS SumOfTotalHoursProvided, Sum(Visits.TotalMinutesProvided) AS SumOfTotalMinutesProvided, Count([Crisis Calls Attended].LogNo) AS [Crisis Calls Attended_LogNo]
FROM [Crisis Calls Attended] INNER JOIN Visits ON [Crisis Calls Attended].[LogNo] = Visits.[LogNo]
GROUP BY [Crisis Calls Attended].ClientName, [Crisis Calls Attended].ClientAddress;

but you have this divide in hours and minutes which will give you some problems!~(

 

[Severin]

I recall a small db I made about minutes and short time you can see if that gives you any inspiration http://www.parodux.com/wp-content/uploads/2010/01/MinutesIntoTime.mdb

 

[STEVENCV]

Thanks again for your help.

I have sorted the grouping and sorting, just by deleting the one that was in there. It then seems to automatically order by log no ascending, which is fine. As you said, it also fixed the parameter issues.

I have attached my updated database, and I still have issues with what you have tried to explain.

1) In the "Total Overall Hours" report, I don't understand what you mean by adding subtotals in a footer under grouping and sorting. I just need to total up each person's total hours/minutes underneath the list of records for that person.

2) In the 'Basic Hours Report", I copied the report above and renamed it, and then changed the control source as you directed. This has not worked, and I don't know why. It has formatted how I wanted i.e. it now shows only one record per person, rather than a list of visits, but I am getting parameter issues and it's not even pulling log numbers across, nevermind adding up the subtotals.

I won't need the date field on this report, so I will delete that at some point.

 

[Severin]

1) make a group with person and select footer then put a sum field on the footer..

2) you have to look at the recordsource, you can change from showing property sheet to showing add existing fields. When you have cleaned up the parameter issue will disappear.

 

[STEVENCV]

Thank you. I managed to get the footer, and that is now adding up correctly.

I am close to having this finished, but I am struggling with your advice for point 2. I have uploaded my database again, could you please explain how to fix it?

I have looked at the source, and it all looks fine to me, so I don't know what to change.

I am sorry to be such a pain.

 

[Severin]

Hi, I'm on a 2007 machine, so I'll have a look tomorrow...

Why they made 2 versions of accdb files is beyond my grasp!~)

 

[STEVENCV]

Thank you. Yes that is a pain. I have a colleague who is trying to open another database but he can't because it's 2010 and he uses 2007.

 

[Severin]

..a quick run through the report, had to make time into minutes... you can do your thing in the totaltime, so you get hours and minutes or use the example I mentioned in an earlier reply!~)