C
Claire
I have a database of employee time sheets.
One table includes the timesheets that are completed- the employee name and
date of the timesheet along with an assigned id that incorporates both fields
(ie, the time sheet for John Q. Doe on 9/1/08 has an id of JQD090108. This
will hopefully keep me from entering the same timesheet twice).
I have another table of employees that lists the date they must begin
filling out timesheets, and the date they are exempt from timesheets (because
of a change in position or leaving the company). There is also a table that
lists the dates of the timesheets as they come due (ie the week ending date
of these timesheets). From the latter two tables I have designed a query
that will list all the timesheets that are required (ie John Doe 9/1/08, John
Doe 9/8/08, Jane Smilth 9/1/08, Jane Smith 9/8/08).
I would like to design a query that lists the timesheets that are not
completed. It should have the name of the employee and the date of the time
sheet they owe. There may be more than one date per employee, and more than
one employee per date.
I have tried to join the timesheet completed table with the timesheet
required query with an outer join. However this is giving me a cartesian
product. It appears that access is not looking for the employee name to
match before comparing the date. When I try to join the employee name at the
same time I get an error that says I have ambiguous outer joins, and to force
one of the joins to be performed to first to create a separate query that
performs the first join and then to include that query in my SQL statement.
I am having a hard time picturing how joining the employee names in a
separate query will factor into the final query.
I have had success looking for the timesheets owed for a specific week, but
I would like to be able to see all of those owed (with the bonus that I could
possibly restrict that query to a certain time frame).
Hopefully all of that makes sense, and thanks for any suggestions you have.
One table includes the timesheets that are completed- the employee name and
date of the timesheet along with an assigned id that incorporates both fields
(ie, the time sheet for John Q. Doe on 9/1/08 has an id of JQD090108. This
will hopefully keep me from entering the same timesheet twice).
I have another table of employees that lists the date they must begin
filling out timesheets, and the date they are exempt from timesheets (because
of a change in position or leaving the company). There is also a table that
lists the dates of the timesheets as they come due (ie the week ending date
of these timesheets). From the latter two tables I have designed a query
that will list all the timesheets that are required (ie John Doe 9/1/08, John
Doe 9/8/08, Jane Smilth 9/1/08, Jane Smith 9/8/08).
I would like to design a query that lists the timesheets that are not
completed. It should have the name of the employee and the date of the time
sheet they owe. There may be more than one date per employee, and more than
one employee per date.
I have tried to join the timesheet completed table with the timesheet
required query with an outer join. However this is giving me a cartesian
product. It appears that access is not looking for the employee name to
match before comparing the date. When I try to join the employee name at the
same time I get an error that says I have ambiguous outer joins, and to force
one of the joins to be performed to first to create a separate query that
performs the first join and then to include that query in my SQL statement.
I am having a hard time picturing how joining the employee names in a
separate query will factor into the final query.
I have had success looking for the timesheets owed for a specific week, but
I would like to be able to see all of those owed (with the bonus that I could
possibly restrict that query to a certain time frame).
Hopefully all of that makes sense, and thanks for any suggestions you have.