G
Grace
I use Access 2003 and a database with the following tables
Table named Employees with the following fields: Payroll ID (this is not an
AutoNumber field), Surname, First Name, Middle Name, Start Date, Days in
First Period
Table named Pay Periods with the following fields: Period ID (AutoNumber
field), Period Start Date (Date/Time field), Period End Date (Date/Time
field) and Total Hours (number field - double, standard, 2 decimal points)
Table named Employee Hours with the following fields: Hours Worked ID
(AutoNumber field), Employee (lookup field selecting employee from the
Employees table), Pay Period (lookup field selecting a pay period from the
Pay Periods table) and Hours Worked field (number field - double, standard, 2
decimal points)
Table named Types of Leave with the following fields: Leave ID, Type
Table named Time Off with the following fields: Time Off ID (AutoNumber
field), Employee (lookup field selecting employee from the Employees table),
Leave Date (Date/Time field) Type of Leave (lookup field selecting type of
leave from the Types of Leave table), Hours (number field - double, standard,
2 decimal points)
Table named Paid Banked Time with the following tables: Paid ID (AutoNumber
field), Employee (lookup field selecting employee from the Employees table),
Pay Period (lookup field selecting pay period from the Pay Periods table) and
Hours Paid Out field (number field - double, standard, 2 decimal points)
I have a query named Banked Hours which calculates the amount of banked time
for each pay period an employee has worked. The resulting fields in this
query are Employee, Pay Period, Total (used an IIf statement to calculate the
number of hours that an employee would have had in the pay period if this was
the first pay period the employee worked), Hours Worked, Banked (difference
between the Total and Hours Worked field).
What I need now is a query combining the banked time, the amount of banked
time an employee has taken and the number of banked hours that an employee
was paid for in each pay period. When I tried to use a LEFT JOIN with an
INNER JOIN, I got an ambiguous outer join error message. As suggested, I
created a separate query that performs the first join and then included that
query in the second query. Attached is the SQL statement for the first
query. I am not sure if I selected the correct Employee field:
I saved the following query as Banked Hours Paid Out 1 query:
SELECT DISTINCT [Paid Banked Time].Employee, [Paid Banked Time].[Pay
Period], [Paid Banked Time].[Hours Paid Out]
FROM [Banked Hours] INNER JOIN [Paid Banked Time] ON [Banked Hours].Employee
= [Paid Banked Time].Employee
ORDER BY [Paid Banked Time].Employee, [Paid Banked Time].[Pay Period];
I then created the following query as Banked Hours Paid Out 2 query:
SELECT DISTINCT [Banked Hours].Employee, [Banked Hours].[Pay Period],
[Banked Hours].Total, [Banked Hours].[Hours Worked], [Banked Hours].Banked,
[Banked Hours Paid Out 1].[Hours Paid Out], [Banked Hours].Staff
FROM [Banked Hours] LEFT JOIN [Banked Hours Paid Out 1] ON [Banked
Hours].[Pay Period] = [Banked Hours Paid Out 1].[Pay Period];
The results of this second query do not make sense. The majority of the pay
periods have 3 employees getting banked time pay. For every employee in a
pay period that gets banked time pay, every employee that worked that pay
period has the correct information for all the fields coming from the Banked
Hours query but recreating that same row of information from the Banked Hours
query but with different numbers in the Hours Paid Out column. The first row
would have hours paid out for one of the employees that got banked time paid
in that pay period, the second row would have the hours paid out for the
second employee, and so on.
Can anyone help me resolve this?
Table named Employees with the following fields: Payroll ID (this is not an
AutoNumber field), Surname, First Name, Middle Name, Start Date, Days in
First Period
Table named Pay Periods with the following fields: Period ID (AutoNumber
field), Period Start Date (Date/Time field), Period End Date (Date/Time
field) and Total Hours (number field - double, standard, 2 decimal points)
Table named Employee Hours with the following fields: Hours Worked ID
(AutoNumber field), Employee (lookup field selecting employee from the
Employees table), Pay Period (lookup field selecting a pay period from the
Pay Periods table) and Hours Worked field (number field - double, standard, 2
decimal points)
Table named Types of Leave with the following fields: Leave ID, Type
Table named Time Off with the following fields: Time Off ID (AutoNumber
field), Employee (lookup field selecting employee from the Employees table),
Leave Date (Date/Time field) Type of Leave (lookup field selecting type of
leave from the Types of Leave table), Hours (number field - double, standard,
2 decimal points)
Table named Paid Banked Time with the following tables: Paid ID (AutoNumber
field), Employee (lookup field selecting employee from the Employees table),
Pay Period (lookup field selecting pay period from the Pay Periods table) and
Hours Paid Out field (number field - double, standard, 2 decimal points)
I have a query named Banked Hours which calculates the amount of banked time
for each pay period an employee has worked. The resulting fields in this
query are Employee, Pay Period, Total (used an IIf statement to calculate the
number of hours that an employee would have had in the pay period if this was
the first pay period the employee worked), Hours Worked, Banked (difference
between the Total and Hours Worked field).
What I need now is a query combining the banked time, the amount of banked
time an employee has taken and the number of banked hours that an employee
was paid for in each pay period. When I tried to use a LEFT JOIN with an
INNER JOIN, I got an ambiguous outer join error message. As suggested, I
created a separate query that performs the first join and then included that
query in the second query. Attached is the SQL statement for the first
query. I am not sure if I selected the correct Employee field:
I saved the following query as Banked Hours Paid Out 1 query:
SELECT DISTINCT [Paid Banked Time].Employee, [Paid Banked Time].[Pay
Period], [Paid Banked Time].[Hours Paid Out]
FROM [Banked Hours] INNER JOIN [Paid Banked Time] ON [Banked Hours].Employee
= [Paid Banked Time].Employee
ORDER BY [Paid Banked Time].Employee, [Paid Banked Time].[Pay Period];
I then created the following query as Banked Hours Paid Out 2 query:
SELECT DISTINCT [Banked Hours].Employee, [Banked Hours].[Pay Period],
[Banked Hours].Total, [Banked Hours].[Hours Worked], [Banked Hours].Banked,
[Banked Hours Paid Out 1].[Hours Paid Out], [Banked Hours].Staff
FROM [Banked Hours] LEFT JOIN [Banked Hours Paid Out 1] ON [Banked
Hours].[Pay Period] = [Banked Hours Paid Out 1].[Pay Period];
The results of this second query do not make sense. The majority of the pay
periods have 3 employees getting banked time pay. For every employee in a
pay period that gets banked time pay, every employee that worked that pay
period has the correct information for all the fields coming from the Banked
Hours query but recreating that same row of information from the Banked Hours
query but with different numbers in the Hours Paid Out column. The first row
would have hours paid out for one of the employees that got banked time paid
in that pay period, the second row would have the hours paid out for the
second employee, and so on.
Can anyone help me resolve this?