R
Ray S.
I query a mainframe source to determine which employees and their managers
have not entered time on their timesheets.
I import the data into MS Access and create a table listing all company
managers, resources (managers/employees/contractors), and hours entered.
I also import a table listing all contractors and their managers.
I query the hours and contractor tables joined on resource to select the
contractors. The query includes criteria to select only contractors who have
not entered any hours.
I also query the hours table to give me all resources who have entered less
than 40 hours.
What I need is a query that will result in all resources that do not have 40
hours, excluding contractors that have entered any hours over zero.
I make the mentioned tables by a series of complicated make table queries
resulting in the fields:unit, group, manager, resource, hrs_entered, and
hrs_required.
The contractor table results in the fields:unit, group, manager, resource
This query gives me the contractors in the hours table:
SELECT tbl_Hours.Manager, tbl_Hours.Resource
FROM tbl_Hours INNER JOIN ContractorList ON tbl_Hours.Resource =
ContractorList.Resource_Name;
Using that query I can go back to the main tbl_Hours and select resources
that are contractors, then use the criteria that their hrs_entered equal zero
to give me the contractors that I need to notify. I can also easily select
their managers.
From the main tbl_Hours I can also easily select managers and resources
where resources have less than 40 hrs_entered. That list, however, includes
the contractors.
How can I exclude contractors with hrs_entered<>0 from a notify query. That
is, I only want contractors who have not entered any hours (hrs_entered=0)
and non-contractors who have hrs_entered<40.
have not entered time on their timesheets.
I import the data into MS Access and create a table listing all company
managers, resources (managers/employees/contractors), and hours entered.
I also import a table listing all contractors and their managers.
I query the hours and contractor tables joined on resource to select the
contractors. The query includes criteria to select only contractors who have
not entered any hours.
I also query the hours table to give me all resources who have entered less
than 40 hours.
What I need is a query that will result in all resources that do not have 40
hours, excluding contractors that have entered any hours over zero.
I make the mentioned tables by a series of complicated make table queries
resulting in the fields:unit, group, manager, resource, hrs_entered, and
hrs_required.
The contractor table results in the fields:unit, group, manager, resource
This query gives me the contractors in the hours table:
SELECT tbl_Hours.Manager, tbl_Hours.Resource
FROM tbl_Hours INNER JOIN ContractorList ON tbl_Hours.Resource =
ContractorList.Resource_Name;
Using that query I can go back to the main tbl_Hours and select resources
that are contractors, then use the criteria that their hrs_entered equal zero
to give me the contractors that I need to notify. I can also easily select
their managers.
From the main tbl_Hours I can also easily select managers and resources
where resources have less than 40 hrs_entered. That list, however, includes
the contractors.
How can I exclude contractors with hrs_entered<>0 from a notify query. That
is, I only want contractors who have not entered any hours (hrs_entered=0)
and non-contractors who have hrs_entered<40.