Run Monthly Payroll (1 Viewer)

Hi Everyone, I am new to MS Access and enjoying my journey into this world of mental gymnastics (aka DB relationship management). I want to solve a problem (WITHOUT PROGRAMMING) and would be eternally grateful if someone could help me. I have the following tables (all tables have primary autonumber IDs):

1. Master Emp (Usually EmpID, First, LAstName etc)
2. Master Salary Table ( EmpID, Basic Salary, Housing Allowance, Effective Date - in case salary details change)
3. Master Period Table (field for each month e.g Jan 2018, Feb 2018 etc- salaries are paid monthly)
4. Addition Type Table (for different additions to payroll like entertainment expenses)
5. Deduction Type Table (for different deductions from payroll like loan deductions etc)

I then have three tables to populate the data:
1. Monthly payroll data includes EmpNo, Period, Basic and Housing
2. Addition Table (for any additions EmpNo, Period, Addition Type and amount)
3. Deduction Table (for deductions EmpNo, Period, Deduction Type and Amount)

Now I am trying to produce a query which will generate monthly payroll with all this info but I seem to be messing it up with the way I am setting up the aggregates for the query. Also I am not sure if a junction table should be in use here (many to many relationship for period, empNo, Add and Deduction )

If anyone can help me, I would be eternally grateful.
Thanks
AJ

Do you want the additions and deletions summarized into 1 Addition and 1 Deduction value for each emp/period?

Build an aggregate query for Additions, GROUP BY EmpNo, Period

Build an aggregate query for Deductions, GROUP BY EmpNo, Period

Build another query that joins the above to Monthly with compound join on EmpNo and Period

If you need the additions and deductions itemized, probably can't do all in query and should build report with subreport(s).


Advise not to use spaces in naming convention, nor punctuation/special characters (underscore only exception).

Thank you June7 for your reply. Awesome of you to take the time.

Yes to your first question. The report that should go to management should have the following columns:
1. EmpNo
2. Period
3.First/Last Name
4.Basic Salary
5. Housing Allowance
6. Travel Allowance (where applicable)
7. Total Additions
8. Gross Pay
9. Total Deductions
10. Net Pay

I have built the aggregate query for Additions and Deductions and it seems to be working fine. I have also done an aggregate query for the MONTHLY which works fine too. And I have done the final query to join all these queries grouping the items 1, 2 3 4 5 6 (from the above table), and summing 7 and 9. But the summing seems to be calculating more than one period (I have put some sample data where for some employees there is no addidtions or deductions for a particular period and for others there are). cant get my head around it.
Yes the conventions you mentioined in your last sentence is what I am adopting.

One particular concept I didnt understand in your reply was the term "compound join"....is this a very specific function in Access?

Many Thanks again for your time
AJ

No, not specific to Access. Compound join is joining datasets in query on 2 or more fields. The query will show multiple link lines. You will have to manually set these links.

A compound join would be required if a db is designed with compound keys - which I avoid passionately and have only resorted to once.

But sometimes queries result in the requirement for compound joins, especially when aggregating data on more than one field.

What is a period - month?