Query logic - linking a table to itself?

G

Guest

I've got a table called EMPLOYEE_DETAIL that tracks people as they move from
store to store. The table has the following fields:
STORE_NUM
SSN (Employee social)
QTR (Quarter)
START_DATE
END_DATE

START_DATE is obviously the date that the employee started working and
END_DATE - if not null - is the date the employee left.

I'm trying to create a query that shows all stores with no employees for
December. For the purposes of this scenario, if a store didn't have employees
on 12/1 then it didn't have any for the month.

I can do something like SELECT * FROM EMPLOYEE_DETAIL WHERE END_DATE <
#12/1/2004# to find employees who left prior to 12/1. I can use the GROUP BY
clause to find a list of stores that had one or more employees leave before
12/1. However, that doesn't take into account that a store might have had
other employees who DIDN'T leave at all.

I think I need to add the table to the query again and link it to itself,
but I'm not quite sure how to make this query behave the way I want.

Access 2000. Thanks.
 
G

Guest

How about something like:

SELECT Store_Num, Count(SSN) as Employees
FROM yourTable
WHERE Start_Date <= #12/1/04#
AND NZ(End_Date, Date()) > #12/1/04
GROUP BY Store_Num
HAVING Count(SSN) = 0

HTH

Basically, what this does is count the number of people assigned to a store
on December 1st. If they don't have an [End_Date], it substitutes the
current date.

The HAVING clause limits the result set to only those stores with 0 employees.

HTH
Dale
 
G

Guest

Dale,

Thanks for the help. I understand the logic of your example, but it doesn't
seem to be working the way you describe. Here is the query (this is the
actual query with all the clauses I need for my example - I had simplified it
for the purposes of my example):

SELECT EMPLOYEE_DETAIL.BRANCH_NBR, Count(EMPLOYEE_DETAIL.SSN) AS NUM_EMP
FROM EMPLOYEE_DETAIL
WHERE
(((EMPLOYEE_DETAIL.START_DATE)<=#12/1/2004#)
AND
((EMPLOYEE_DETAIL.QTR)="4")
AND
((EMPLOYEE_DETAIL.JOB_CODE) In ("J11","J42","J43","J44","J45","J66","J67"))
AND
((NZ([END_DATE],Date()))>#12/1/2004#))
GROUP BY EMPLOYEE_DETAIL.BRANCH_NBR
HAVING (((Count(EMPLOYEE_DETAIL.SSN))=0));

To the best I can tell, it's not displaying the branches that meet the
criteria, instead of showing them with a count of 0. If I remove the grouping
and counting but leave all the WHERE clauses, the result set skips over
branches that meet all of them. I know there's at least one branch that fits
the scenario.

Dale Fye said:
How about something like:

SELECT Store_Num, Count(SSN) as Employees
FROM yourTable
WHERE Start_Date <= #12/1/04#
AND NZ(End_Date, Date()) > #12/1/04
GROUP BY Store_Num
HAVING Count(SSN) = 0

HTH

Basically, what this does is count the number of people assigned to a store
on December 1st. If they don't have an [End_Date], it substitutes the
current date.

The HAVING clause limits the result set to only those stores with 0 employees.

HTH
Dale

MDW said:
I've got a table called EMPLOYEE_DETAIL that tracks people as they move from
store to store. The table has the following fields:
STORE_NUM
SSN (Employee social)
QTR (Quarter)
START_DATE
END_DATE

START_DATE is obviously the date that the employee started working and
END_DATE - if not null - is the date the employee left.

I'm trying to create a query that shows all stores with no employees for
December. For the purposes of this scenario, if a store didn't have employees
on 12/1 then it didn't have any for the month.

I can do something like SELECT * FROM EMPLOYEE_DETAIL WHERE END_DATE <
#12/1/2004# to find employees who left prior to 12/1. I can use the GROUP BY
clause to find a list of stores that had one or more employees leave before
12/1. However, that doesn't take into account that a store might have had
other employees who DIDN'T leave at all.

I think I need to add the table to the query again and link it to itself,
but I'm not quite sure how to make this query behave the way I want.

Access 2000. Thanks.
Click to expand...
Click to expand...
 
D

Dale Fye

Actually, I think the problem is that if there was no one in the branch that
meets the requirements, then there is no way to get the appropriate list
from EmployeeDetails table. So, lets try a different attack. Create a
nested subquery that returns a list of all the Branch numbers where at least
one employee meets the criteria.

What is the purpose of your Quarter field? Do you have a record in
Employee_Detail for each employee, for each quarter? If not, I think this
field could be left out, because it could always be calculated using the
DatePart() function. If this doesn't work, try putting the Quarter criteria
back in.

I assume you have a table of branch numbers (tbl_Branches). Try the
following and see what you get.

SELECT B.Branch_NBR, B.Branch_Name
FROM tbl_Branches B
WHERE B.Branch_NBR NOT IN
(SELECT DISTINCT ED.BRANCH_NBR
FROM EMPLOYEE_DETAIL ED
WHERE ED.START_DATE<=#1/1/05#
AND NZ(ED.END_DATE,Date()) > #12/1/2004#
AND ED.JOB_CODE In ("J11","J42","J43","J44","J45","J66","J67"))

This should give you a list of every branch that did not have any employees
that were employed in the branch at least one day during the month of
December, where were in one of the appropriate job codes.

HTH
Dale




MDW said:
Dale,

Thanks for the help. I understand the logic of your example, but it doesn't
seem to be working the way you describe. Here is the query (this is the
actual query with all the clauses I need for my example - I had simplified it
for the purposes of my example):

SELECT EMPLOYEE_DETAIL.BRANCH_NBR, Count(EMPLOYEE_DETAIL.SSN) AS NUM_EMP
FROM EMPLOYEE_DETAIL
WHERE
(((EMPLOYEE_DETAIL.START_DATE)<=#12/1/2004#)
AND
((EMPLOYEE_DETAIL.QTR)="4")
AND
((EMPLOYEE_DETAIL.JOB_CODE) In ("J11","J42","J43","J44","J45","J66","J67"))
AND
((NZ([END_DATE],Date()))>#12/1/2004#))
GROUP BY EMPLOYEE_DETAIL.BRANCH_NBR
HAVING (((Count(EMPLOYEE_DETAIL.SSN))=0));

To the best I can tell, it's not displaying the branches that meet the
criteria, instead of showing them with a count of 0. If I remove the grouping
and counting but leave all the WHERE clauses, the result set skips over
branches that meet all of them. I know there's at least one branch that fits
the scenario.

Dale Fye said:
How about something like:

SELECT Store_Num, Count(SSN) as Employees
FROM yourTable
WHERE Start_Date <= #12/1/04#
AND NZ(End_Date, Date()) > #12/1/04
GROUP BY Store_Num
HAVING Count(SSN) = 0

HTH

Basically, what this does is count the number of people assigned to a store
on December 1st. If they don't have an [End_Date], it substitutes the
current date.

The HAVING clause limits the result set to only those stores with 0 employees.

HTH
Dale

MDW said:
I've got a table called EMPLOYEE_DETAIL that tracks people as they move from
store to store. The table has the following fields:
STORE_NUM
SSN (Employee social)
QTR (Quarter)
START_DATE
END_DATE

START_DATE is obviously the date that the employee started working and
END_DATE - if not null - is the date the employee left.

I'm trying to create a query that shows all stores with no employees for
December. For the purposes of this scenario, if a store didn't have employees
on 12/1 then it didn't have any for the month.

I can do something like SELECT * FROM EMPLOYEE_DETAIL WHERE END_DATE <
#12/1/2004# to find employees who left prior to 12/1. I can use the GROUP BY
clause to find a list of stores that had one or more employees leave before
12/1. However, that doesn't take into account that a store might have had
other employees who DIDN'T leave at all.

I think I need to add the table to the query again and link it to itself,
but I'm not quite sure how to make this query behave the way I want.

Access 2000. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Dale.

After much hemming and hawing, I eventally found my way to a very similar
solution. :)

Dale Fye said:
Actually, I think the problem is that if there was no one in the branch that
meets the requirements, then there is no way to get the appropriate list
from EmployeeDetails table. So, lets try a different attack. Create a
nested subquery that returns a list of all the Branch numbers where at least
one employee meets the criteria.

What is the purpose of your Quarter field? Do you have a record in
Employee_Detail for each employee, for each quarter? If not, I think this
field could be left out, because it could always be calculated using the
DatePart() function. If this doesn't work, try putting the Quarter criteria
back in.

I assume you have a table of branch numbers (tbl_Branches). Try the
following and see what you get.

SELECT B.Branch_NBR, B.Branch_Name
FROM tbl_Branches B
WHERE B.Branch_NBR NOT IN
(SELECT DISTINCT ED.BRANCH_NBR
FROM EMPLOYEE_DETAIL ED
WHERE ED.START_DATE<=#1/1/05#
AND NZ(ED.END_DATE,Date()) > #12/1/2004#
AND ED.JOB_CODE In ("J11","J42","J43","J44","J45","J66","J67"))

This should give you a list of every branch that did not have any employees
that were employed in the branch at least one day during the month of
December, where were in one of the appropriate job codes.

HTH
Dale




MDW said:
Dale,

Thanks for the help. I understand the logic of your example, but it doesn't
seem to be working the way you describe. Here is the query (this is the
actual query with all the clauses I need for my example - I had simplified it
for the purposes of my example):

SELECT EMPLOYEE_DETAIL.BRANCH_NBR, Count(EMPLOYEE_DETAIL.SSN) AS NUM_EMP
FROM EMPLOYEE_DETAIL
WHERE
(((EMPLOYEE_DETAIL.START_DATE)<=#12/1/2004#)
AND
((EMPLOYEE_DETAIL.QTR)="4")
AND
((EMPLOYEE_DETAIL.JOB_CODE) In ("J11","J42","J43","J44","J45","J66","J67"))
AND
((NZ([END_DATE],Date()))>#12/1/2004#))
GROUP BY EMPLOYEE_DETAIL.BRANCH_NBR
HAVING (((Count(EMPLOYEE_DETAIL.SSN))=0));

To the best I can tell, it's not displaying the branches that meet the
criteria, instead of showing them with a count of 0. If I remove the grouping
and counting but leave all the WHERE clauses, the result set skips over
branches that meet all of them. I know there's at least one branch that fits
the scenario.

Dale Fye said:
How about something like:

SELECT Store_Num, Count(SSN) as Employees
FROM yourTable
WHERE Start_Date <= #12/1/04#
AND NZ(End_Date, Date()) > #12/1/04
GROUP BY Store_Num
HAVING Count(SSN) = 0

HTH

Basically, what this does is count the number of people assigned to a store
on December 1st. If they don't have an [End_Date], it substitutes the
current date.

The HAVING clause limits the result set to only those stores with 0 employees.

HTH
Dale

:

I've got a table called EMPLOYEE_DETAIL that tracks people as they move from
store to store. The table has the following fields:
STORE_NUM
SSN (Employee social)
QTR (Quarter)
START_DATE
END_DATE

START_DATE is obviously the date that the employee started working and
END_DATE - if not null - is the date the employee left.

I'm trying to create a query that shows all stores with no employees for
December. For the purposes of this scenario, if a store didn't have employees
on 12/1 then it didn't have any for the month.

I can do something like SELECT * FROM EMPLOYEE_DETAIL WHERE END_DATE <
#12/1/2004# to find employees who left prior to 12/1. I can use the GROUP BY
clause to find a list of stores that had one or more employees leave before
12/1. However, that doesn't take into account that a store might have had
other employees who DIDN'T leave at all.

I think I need to add the table to the query again and link it to itself,
but I'm not quite sure how to make this query behave the way I want.

Access 2000. Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sub query count 8
Lookup wizard and same table fields in the query 3
Function "Workingdays" query error date 6
Not In (sort of) query 1
Help - calculating averages 1
Query Parameters 1
Help with crosstab query 2
Show an Employee with no reviews 6

Top