Totalling (1 Viewer)

[cymrudesign]

i thik i need a total rethink ( formally Totalling)

Hi i have 31 combo boxes on a report, names 1st 2nd etc for days of the month can anyone give me the proper syntax to total up all the boxes in a report i just cant get it to do it

 

[raskew]

Hi -

31 combo boxes in a report? Perhaps if you'd explain a little more what you're trying to accomplish.

Bob

 

[cymrudesign]

its a scheduling / invoice report ....i had to add the combo boxes as putting in the various dates for the month for each client would prove very time consuming .. if i can total these combo boxes it will do exactly what they want it to do ( nevermind how wacky ) just cant work the syntax out . each combo box can contain 0.25 to 10 and i just need them totalling

 

[raskew]

Hi -

Still can't understand putting a combo box in a report. Do you perhaps mean a form (where'd you able to manipulate the combo box)?

Bob

 

[cymrudesign]

they are on the form they just appear as combos on the reports thats all

 

[raskew]

In your form's query, add a calculated field (assuming your combo boxes are named [Day1] thru [Day31].
Total: [Day1]+[Day2]+[Day3]+[Day4]+[Day5]+[Day6]+[Day7]+[Day8]+[Day9]+[Day10]...[Day31]

Add a text box to your report, with Control Source: Total

Consider converting your report's combo boxes to text boxes since they serve no purpose other than to take up space.
Combo boxes are intended to allow users to make choices--great on a form, useless and very likely unworkable on a report.

Bob

 

[cymrudesign]

cheers will give it a go many thanks

 

[cymrudesign]

wel i have given it a go and im going to have to admit defeat on this one if anyone knows how i can do this ( i have no knowledge of VB at all ) it would be greatly appreciated . The problem with adding up in the previous solution by raskew is that i have a field for each day and the query is adding the total amount of hours for all clients on that day .

i have 2 tables one with patient details and one that should store visit information for the days in that month . most of the visit information will not change from month to month apart from cancellations or additonal hours . so basically the report will be the same hours just different months
visits are X hrs per day no time information at all .

the report needs to list all days of the month even if there have been no visits at all , and total them up for the month .
unless we can work out how to add the filds in the report this is

i was asked to do this as a favour and since they are non profit i thought i would do a good deed .....can anyone help me with this one ? Please aaaaaaahhhh ;-)

 

[Rich]

I did tell you before, you don't need thirty one text boxes, it's not that difficult to create a temporary list of days in any month and then to use a Union or Crosstab query to bring it all together

 

[cymrudesign]

ok union and crosstab never been there....im totally in the dark here. i didnt think it would be so difficult ....hence my first version that you manually entered the date of the visit into, this worked untill the next month where the user had to change 30/31 dates for each patient . ;-)

 

[Rich]

Surely they should only enter dates that are relevant?

 

[cymrudesign]

they should yes but even then they would have to forward the dates to the next month ..this is why i tried the pull down idea ....... this data does not even have to be recalled ...it just needs to print out every month and be moved on one month ( this is why i thought it would be doable ) .....all of the dates for the month are printed and some are empty of hours and some have data against them ..........odd i know ....im totally stumped now , ;-)

 

[Rich]

So are you saying that the same patients visit each month?

 

[raskew]

Hi -

A Rant--

I often wish that developers who pride themselves on theoretically correct programming skills could be offered the opportunity to spend a few days inputting data using their squeaky-clean techniques, in a high-speed environment with a deadline hanging over their heads. In my experience they produce technically correct 'clunkers'!

Examples:

Calendar Controls. They look gee-whiz and work great if you’re working with the current or previous month. Conversely, they absolutely suck if one is required to input the date of birth (DOB) of a seventy-two year old nursing home patient. Clickety-Clickety-Clickety gets old real fast, while a formatted text box works just really fine, in a fraction of the time.

Combo Boxes. Great for restricting the user’s options but absolutely hideous if the user is required to choose a day between 1 and 31. Provided the user knows the potential options, a formatted text box improves speed and accuracy many-fold.

Normalization and Spreadsheet Mentality. In MHO, there’s a place for a non-normalized solution. Take this thread: Imagine a scenario where a patient/client is receiving both residential and non-residential services. Residential services are a piece of cake: client is admitted on x date, discharged on y date—a simple DateDiff() equation sorts that out with a separate record for each day of their stay. If, however, the client is also receiving non-residential services, from multiple caregivers, on an irregular basis, then a subform linked to the client, with caregiver combo, service combo, and Days 1 – 31 (each with a default value of 0) makes perfect sense. The user can input the mm/yyyy, caregiver and service code and then skim across the form adding service units where needed, with a calculated totals field to show the number of service units provided during the mm/yyyy. Denormalized, you betcha, but it takes very little programming skill to convert this denormalized record into a fully normalized table in a matter of seconds, disregarding the unused dates. Consider the ‘normalized’ option: Enter the Caregiver, Service, and a date, then the number of units. Then do it again for the next date, and again and again and again. Don’t know about you, but that doesn’t sound like a ‘good deal’ to me.

End of Rant!

Bob

 

[cymrudesign]

So are you saying that the same patients visit each month?

The majority of the patients either have the same amount of hours per day on the same days ( or day intervals) each month so apart from amendments or cancellation the reports would be the same just the month would be different .

 

[cymrudesign]

raskew I must agree that usabilty has to be a fundamental feature of any solution designed . The problem i had was that they required to have all of the months dates listed , even if the patient was not visited . This in turn had its own problems with data input. So in my mind i thought that having the days listed with combo boxes for the hours would be a solution, due to my ignorance i found a whole other set of problems . So thats 1-0 to the practice of planning the job beforehand .

 

[Rich]

I'm sure Pat Hartman posted an example here of entering data in a spreadsheet like manner, have a look in the samples forum, it might give you some ideas

 

[cymrudesign]

i did have my subform in datasheet view in version 1 this did work untill all the dates had to be cleared and new dates for each client entered the beggining of the month. I was trying to avoid that as it would be just as time consuming as the present 'by hand method ' if you have any theories on how it should be done im willing to scrap it all and start again ;-)

 

[raskew]

'The majority of the patients' do this or that. The problem is that not all of the patients do this this or that. Result: You're going to have examine the status of each patient, each month. Give up on the idea that you can duplicate a patient's previous month's results. It's not going to fly without examination of this month's situation.

Bob

 

[cymrudesign]

i would have though that you would only have to change each months details if they have cancelled an appointment or increased their hours. the previous months totals would not have to be kept, as records will be kept on hard copy.

I.E if joe bloggs gets a visit of an hour every other day and his situation hasnt changed his report would be the same apart from the month.

or am i going insane ;-)

Yes i am going insane ...of course the day of the visit would change as the months progressed .....ok double stumped now anyone any idea how i could approach this even ?