Sum monthly totals

[mudz78]

Hi,

I am having a few troubles .

I have an access table that displays employee pays based on the (pay week) ending date.

For example:

Name WeekEnding Pay Hours
Joe Bloggs 2-1-05(Sun) $550 35
Joe Bloggs 9-1-05(Sun) $550 35
Joe Bloggs 16-1-05(Sun) $550 35
Joe Bloggs 23-1-05(Sun) $550 35
Joe Bloggs 30-1-05(Sun) $550 35
IC Green 16-1-05(Sun) $550 35
Jane Doe 23-1-05(Sun) $100 5



The week ending is each and every Sunday, but employees do not neccessairly has an entry for each week (in case that matters).

The table contains other information, such as an employee Tax File Number(TFN), a unique employeeID (which changes every time an employee receives a change in payrate...stupid I know when we have a unique TFN)...a payrate received for that given week (based on employeeID), as well as a unique transaction number.

Just to re-iterate: when employees receive, for example, a pay raise, then their details must be re-entered into the DB with payrate for that unique (and now new) employeeID. Obviously their TFN remains the same however.


I would like to do a select query that sums the earnings for each month, for each employee.

So,

Name Period Earnings Hours
Joe Bloggs Jan 2005 $2750 175
Joe Bloggs Feb 2005 $2200 140
Jane Doe Jan 2005 $100 5
Jane Doe Feb 2005 $2200 140
IC Grenn Jan 2005 $2400 150
IC Grenn Jan 2005 $2100 130

The only really important information is the employee (which I assumed will have to be based of the TFN and NOT the employeeID) the month and the amount earnt in that month.

It seems like a simple enough idea, but I can't get it off the ground.

Any advice is appreciated.

Thanks for your time

-John

 

[Norie]

John

To return the month in an expresion you can use either

NumMonth:Month([WeekEnding])

or

TextMonth:Format([WeekEnding], "mmm")

To return the year.

NumYear:Year([WeekEnding])

You could group by these expressions and sum Earnings in a totals query.