If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Left Join Question
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? |
#2
|
|||
|
|||
Left Join Question
On Jul 6, 10:02*pm, Grace wrote:
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. |
#3
|
|||
|
|||
Left Join Question
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. " wrote: On Jul 6, 10:02 pm, Grace wrote: 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. |
Thread Tools | |
Display Modes | |
|
|