Calculating YTD totals in a report

[msmit8]

I’m creating a Payroll database and I’m having trouble with a report. In my report, I have five additional Year to Date (YTD) fields that are not in my table (italicized in below – see Report) and I’m not sure how to get them to calculate a YTD total on the report.

Table: Employee
Contains
· Employee Name

Table: Payroll Entry

Note: The person using this database will manually calculate the italicized fields

Contains
· ID
· Employee Name
· Payroll Start Date
· Payroll End Date
· Hours Worked
· Wages
· FICA
· Federal Withholding
· State Withholding
· Net Pay

Reports: Report
Contains
· Employee Name
· Payroll Start Date
· Payroll End Date
· Hours Worked
· Wages
· FICA
· Federal Withholding
· State Withholding
· Net Pay
· YTD Wages
· YTD FICA
· YTD Federal Withholding
· YTD State Withholding
· YTD Net Pay

I’ve attached a copy of my database for your reference.

Thanks for any help you can offer.

Matt

 

[plog]

First, clean some things up. Don't use anything but underscores and alphanumeric characters in your names (table, queries, forms, fields, reports). Also, you shouldn't have an [Employee Name] field in Payroll Entry. You should have an EmployeeID field as an autonumber primary key in Employee and use that value instead of the name in Payroll Entry. Those two things will coding simpler, as well as make you not have a headache when Amy gets married halfway through the year.

Then to get YTD values you would use the DSUM function (http://www.techonthenet.com/access/functions/domain/dsum.php) to sum up all the values in the PayrollEntry table that are up to and before the record you are dealing with. They key is using the correct criteria argument and a key to that is not using names but the EmployeeID I described above.

Give it a shot, look over the documentation I provided and post back here any questions.

 

[msmit8]

Thanks for your reply. I made all of your suggested changes to my database.

However, I'm lost when it comes to using the DSUM function.

Is this function to be used in my Report or Querie? And if so, I don't even know where to start to create the DSUM function. Maybe I missed it in the link you provided.

I've attached an updated copy of my database.

Thanks again,
Matt

 

[plog]

This is how the DSUM works: It takes 3 arguments: the first is the field you want to sum, the second is the table\query that field is in, the third is criteria to limit how many records you sum.

I suggest you start by creating a query based on the table that holds the data you want to sum. Then add a simple DSum field that doesn't use any criteria. The DSum would look like this:

FieldYTD: DSum("[FieldToSumHere]", "TableThatFieldIsIn")

Get that to work and then add the criteria argument.

 

[msmit8]

Thanks again for your reply. I started by try to sum my Wages field, but I'm not getting any results when I run the Querie.

My expression looks like this:

YTDWages: DSum("[Wages]","PayrollEntry]")

My criteria is:

["EmployeeID?"]

Any suggestions?

I've attached my most recent database for your reference.

Thanks again,
Matt

 

[plog]

YTDWages: DSum("[Wages]","PayrollEntry]")

There's no criteria argument in there, so I don't know what you mean by ["EmployeeID?"] being your criteria. When I say criteria I mean the third argument of the DSUM:

ExampleField: DSum(FieldNameHere, TableNameHere, CriteriaHere)

For now, I am having you run the DSum without criteria just so you see that you can get it to work (aka, produce a number).

The error with yours is an unmatched bracket. Look at your DSum, you have 3 brackets in it--you should always have an even number of brackets and parenthesis in code.

 

[msmit8]

That was just a typo...YTDWages: DSum("[Wages]","[PayrollEntry]")

Once I run the Querie, I get results, I think, I was expecting.

When I say My criteria is: ["EmployeeID?"], I mean that I thought I can use that to only return results for a specific employee.

Thanks again,
Matt

 

[plog]

Let's not bite off too much at one time--Don't put any criteria on this query at all. Get the DSum to work properly then if needed, build another query using this query as its data source. So from now on, the term 'criteria' means the 3rd argument in the DSum.

Once YTDWages is showing a number, its time to apply criteria to your DSum. I believe you want to use the [EmployeeID] field to limit it. So your criteria will look like this:

"[EmployeeID]=" & [EmployeeID]

Doing this will make sure that you only DSum the records where the EmployeeID in PayrollEntry matches the EmployeeID in the current record of your query.

 

[msmit8]

I was able to get it to run properly and add in all my other expressions. I'm all set. Thanks for your time and help with my issues.

Matt