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
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