Access Report fails to sum the total amount

Report LayoutEarnings PropertiesSum of EarningsReport Header and FooterReport Header and FooterHi,
  I have a report in access where it calculates earnings and getting total amount  of earnings.
  The last column - earnings: Control Source is =[flatrate]+[HourlyTotal] and Name is earnings.
  There is no problem with this. It displays the results fine. The problem is with
  The control that is supposed to add up all the numbers in "earnings"  is Sub Total and its Name and Control Source is as shown above.
  When I run this report, as it is "=Sum([earnings])", it prompts me to enter the value for "earnings". But if I replace "=Sum([earnings])" with "[earnings],  then it runs the report but it only displays the value of earnings field from the last record, instead of total of all earnings.

Can you help?
LVL 1
sgleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
You need to use

Sum([flatrate]+[Hourly Total])

OM Gang
0
sgleeAuthor Commented:
PromptReportThe same result when I put in =Sum([flatrate]+[Hourly Total]).
This time it prompts for Hourly Total. When I enter 100, the sub total show $100 every sub total.
0
omgangIT ManagerCommented:
I missed it before but your [Hourly Total] is also a calculated control on the form.  What is the formula for [Hourly Total]?  Basically, the calculated controls in the footers will need to calculate the values you want based upon the recordsource for the report.  Those other calculated controls on the report don't exist in the recordsource.

OM Gang
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sgleeAuthor Commented:
Hourly Total Control PropertYes Hourly Total is also a formula
0
omgangIT ManagerCommented:
So then make your SubTotal formula

Sum([flatrate]+(whatever the formula for Hourly Rate is)

I can't see the entire formula in the image you posted but your SubTotal formula will look something like
Sum([flatrate]+(iif([Overtime]=-1,[billable_rate]*1.5*([load_in]+[load_out]),[billable_rate]......)

Try that.
The other, perhaps much simpler, option is to add the calculated fields to the query behind the report, e.g. add an [Hourly Rate] field to the query.  Then you can reference just the way you want to in the report.
OM Gang
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sgleeAuthor Commented:
HourlyTotal in QueryHourly Total Control PropertyReportYou were right. I added a new column in the query (for the report) and it worked.

Thank you!
0
omgangIT ManagerCommented:
You're welcome.
OM Gang
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.