By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,481 Members | 3,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,481 IT Pros & Developers. It's quick & easy.

Summing totals in a query

P: 59
I have a database which stores the weekly hours and pay of subcontractors. The WeeklyPayments table has SubContractorID, Month, WeekEnding and Mon, Tue, Wed, etc to Sun showing the hours worked. In a form I have TotalHours, GrossAmount, LessTax and NetAmount calculated fields.

At the end of each month a tax submission is submitted showing the GrossAmount paid in each Month and the amount of TaxDeducted (another calculated field).

I've been trying to find a way to create another form to store the sum of the GrossAmount for each individual month from the WeeklyPayments table. I created a query with the Dsum function
Expand|Select|Wrap|Line Numbers
  1. DSum("[GrossAmount]","WeeklyPayments","[Surname]='" & [Surname] & "'")
This sums all the months together - not individually. Is there another function or way around where I can sum each month separately?
Apr 5 '17 #1
Share this Question
Share on Google+
8 Replies


PhilOfWalton
Expert 100+
P: 1,430
If your description is correct, your data is not normalised which will give problems.

You should have a table of Dubcontractors with their detail - Name, Pay Rate LastPayReviewDate, Tax Code etc. and a Table of Dates and hours worked with SubContractorID as the Foreign Key

Using the info in the latter table you can calculate Pay (= HoursWorked * PayRate).
You can sum these for any period by grouping in a date range or using the Month function. You can find a week no for each date and group on that.
Endless possibilities, but you will need a query to display the results

Phil
Apr 5 '17 #2

P: 59
Phil, yes that's the way I've done it. A SubContractor table with SubContractorID as the foreign key. My original table was the TaxMonth in which I manually input the month end amounts. This consists of the GrossAmountPaid and MonthEnd date, ie 5 Apr 17 which would be the total of all payments made between 1 and 31 March from the GrossAmount in the WeeklyPayments table. If I put the MonthEnd into the WeeklyPayments table how do I still get a sum of that particular month only? Sorry If I'm not following you.
Apr 7 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Can you show me an image of the relationship page with all the tables full length so that I can see all the fields.

Phil
Apr 7 '17 #4

NeoPa
Expert Mod 15k+
P: 31,494
PhilOfWalton:
Endless possibilities, but you will need a query to display the results
Hi Glat.

Pay particular attention to that sentence. Forms hold no data. They display data. This can be based off a table or a query but you're likely to need a query. These concepts are important to understand if you want to progress with this. Wrong-thinking can slow you down a great deal.

You need to focus on getting the data right with Phil's help first, before moving on to handling the form.

Good luck.
Apr 7 '17 #5

P: 59
Phil, copy attached.

Attached Images
File Type: jpg Capture-4.jpg (38.3 KB, 85 views)
Apr 10 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
Thanks for that, It's not as bad as I feared.
One obvious problem it that MonthEnding appears in 2 separate tables, and may or nay not have the same value. Do you have a formula for MonthEnding such as the last Friday in the month, and does it hold true for public holidays?

Neopa is spot on that forms usually show the results of queries. Depending on the data entered, results can be worked out.

I still believe that your weekly payments should in fact just hold the date and the hours worked. If you want to see a Day on your form, the underlying query would have
Expand|Select|Wrap|Line Numbers
  1. format(WorkDate,"ddd")
  2.  
Calculations of pay in any period would using a Totals Query and grouping dates by week number and summing gours worked * pay rate

Phil
Apr 10 '17 #7

P: 59
Thank you Phil. The MonthEnding in WeeklyPayments was there for a trial and should have been removed before I sent the screenshot.

My calculations are done in the form via the Query Builder. I have now managed to get what I am looking for, with your help, through a separate crosstab query grouping on Surname with a column heading of MonthDate and Sum Value of GrossAmount. Now just trying to set up my report. Thank you to both for your help.
Apr 11 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
Glad to hear that you're nearly there

Phil
Apr 11 '17 #9

Post your reply

Sign in to post your reply or Sign up for a free account.