Left Join Question

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

 

[pietlinden]

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 calculatethe
number of hours that an employee would have had in the pay period if thiswas
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?

Not much of this makes sense, really.
Why is the total for the Banked Hours not a totals query grouping by
EmployeeID?
Why are you using a DISTINCT instead of a totals query?

If you use a totals query, you will get a single record for each
unique combination of any fields that you are grouping by. If you
group by just Employee ID and sum the hours, you should be most of the
way home. Then you can do that with another query, and as long as you
use EmployeeID as the group by column, you can join back to the
Employee table and do pretty much whatever kind of math you want.

 

[Grace]

I am actually confused by your response for help. The Total in the Banked
Hours query was to calculate the number of hours the employee could have
actually have worked in the pay period since there are times when there are
new employees and they would not or could have worked the number of hours
that is listed in the Total Hours field of the Pay Periods table for the pay
period. I do not want to find out the total number of hours that an employee
has banked, the total number of banked hours that an employee has taken or
the total number of banked hours has been paid for without taking time off.
I want to be able to look at what an employee has banked, paid out or taken
on a pay period basis. The only reason I added a DISTINCT was because I was
getting duplicate rows of the same information. The reason that I used
Employee in my other tables and queries is because it was the name of the
field which I used when I created an Employee lookup field in the tables.

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?

Not much of this makes sense, really.
Why is the total for the Banked Hours not a totals query grouping by
EmployeeID?
Why are you using a DISTINCT instead of a totals query?

If you use a totals query, you will get a single record for each
unique combination of any fields that you are grouping by. If you
group by just Employee ID and sum the hours, you should be most of the
way home. Then you can do that with another query, and as long as you
use EmployeeID as the group by column, you can join back to the
Employee table and do pretty much whatever kind of math you want.