Your question makes sense. The problem you have is that you may have multiple
years of data. Right now you can only store the managers for quarters (so you
are limited to having managers for one year)
The structure I proposed would have up to four records for each employee
Employee : Your employee
Qtr: a number from 1 to 4 representing the quarter
Manager : what you were putting in Q1 or Q2 or Q3 or Q4
Assuming that you make the change to the above structure and change the field
name to Activity_Date, then the query would look something like
Parameters [Enter the Year] Short;
TRANSFORM Count(S.Manager) as HadOneOrMore
SELECT E.Employee, Count(Q.QTR) as CountQuartersWithSitDown
FROM All_Employees as E INNER JOIN
(SELECT Employee, qtr, Manager
FROM All_Employees) as Q
On E.Employee = Q.Employee LEFT JOIN
(SELECT DISTINCT Employee, Manager, DatePart("q", S.Date) as SitDownQuarter
FROM Supervisor_Activity as S
WHERE S.Date Between DateSerial([Enter the Year],1,1)
and DateSerial([Enter the Year],12,31) as S
ON Q.Employee = S.Employee
AND Q.qtr = S.SitdownQuarter)
GROUP BY E.Employee
PIVOT q.Qtr in (1,2,3,4)
A better structure for tracking Employees and there managers would be a table
like the following where you could track managers and employees over multiple
years:
EmployeeManagementPeriods
EmployeeID - Don't use names. Names get duplicated even in small organizations
ManagerID - Don't use names. Names get duplicated
StartofManagementPeriod
EndManagementPeriod
There is still the problem that you could not generate the report you want.
Since you are reporting on managers and the employees they are supposed to
have sitdowns with. If an employee changes managers, the both managers will
show up as not having had a sitdown with the employee for any quarter where
they were not the manager. In other words, you may need to remove the
managers from the equation or find a way to mark quarters where the manager
was not responsible for the employee.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Tina said:
I can change the All_Employees table structure to the one you suggested.
My only concern with this is what to do if an employee switches managers and
how to show the prior work was done under a prior manager. Does that
question make sense?
I guess I don't quite understand the structure. The Q fields are filled in
with the assigned manager for that quarter. If I have a manager field would I
change it to reflect the current Quarter?
I can also change the field named "DATE" to ACTIVITY_DATE.
Can you show me how it can be one query?
:
OUCH! Your structure makes this tough to do. The All_Employees table should
be structured as three fields:
Employee
Quarter
Manager
Step 1 is to create a query that will give us that structure for purposes of
this report.
SELECT Employee, 1 as qtr, Q1 as Manager
FROM All_Employees
UNION ALL
SELECT Employee, 2 as qtr, Q2 as Manager
FROM All_Employees
UNION ALL
SELECT Employee, 3 as qtr, Q3 as Manager
FROM All_Employees
UNION ALL
SELECT Employee, 4 as qtr, Q4 as Manager
FROM All_Employees
Step2 is to create a query that returns the information for reviews. This
will return one record for each quarter that had one or more reviews.
Parameters [Enter the Year] Short;
SELECT DISTINCT Employee, Manager, DatePart("q", S.Date) as SitDownQuarter
FROM Supervisor_Activity as S
WHERE S.Date Between DateSerial([Enter the Year],1,1)
and DateSerial([Enter the Year],12,31)
NOW using those two saved queries and the All_Employees table you can consruct
a crosstab query that should give you the desired results
TRANSFORM Count(S.Manager) as HadOneOrMore
SELECT E.Employee, Count(Q.QTR) as CountQuartersWithSitDown
FROM All_Employees as E INNER JOIN qSavedUnionQuery as Q
On E.Employee = Q.Employee LEFT JOIN (qSavedHadReview as S
ON Q.Employee = S.Employee
AND Q.qtr = S.SitdownQuarter)
GROUP BY E.Employee
PIVOT q.Qtr in (1,2,3,4)
This could all be done in one query is your structure was correct AND if you
hadn't used Date as a field name. Date is a reserved word and as a function
it returns the current system date. A better name for the field would be
ActivityDate.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Tina S wrote:
I have a few hierarchy tables.
I have an table called ALL_EMPLOYEES.
Field Names and Datatypes: Employee (Text), Q1 (Text), Q2 (Text), Q3 (Text),
Q4(Text).
The Qs, I fill in after each quarter with the assigned Manager's name. This
is how I know a sit along should have been done that quarter.
I also have a table called MANAGERTable.
Field Names and Datatypes: MANAGER (text), SUPERVISOR (text)
Let me know if more is needed. Thanks.
:
Tina,
You need at least on more table with employee and supervisor and supervision
period. If you don't have that data available, then you won't be able to show
employees that have no sitdowns during a specified period. So if John Spencer
never has a sitdown in 2009 then John Spencer won't show up if you are using
only the Supervisor_Activity table.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Tina S wrote:
Table Name: SUPERVISOR_ACTIVITY
Table Field Names and Datatype:
EMPLOYEE (text), MANAGER (text), SUPERVISOR (text), DATE (date/time),
UNIQUE_ID (text), COMMENTS1 (text), COMMENTS2 (text)
The "UNIQUE_ID" is the employee, manager and date combined.
:
A crosstab query will do it but without knowing your table structure I can
not assist any further.
If you post the table and field names with datatype then someone can give
you more help if you need it.
:
A manager needs to sit with each of their employees once a quarter. In the
past, we have said if a manager has 8 employees, they need to have 8 sit
alongs each quarter.
The problem with this is, it does not take into account that a manager may
sit with one person two times and not at all with another employee. So the
manager has 8 sit alongs, but actually only sat with 7 employees. How can I
build a query to only count a person 1 timer per quarter and to also show a
person on the same report that did not get a sit along.
The report for this should list the manager and all of his employees under
him, like this.
Manager Qtr 1 Qtr2 Qtr 3 Qtr 4 YTD
Employee1 1 1 0 1 3
Employee2 0 0 0 0 0
Employee3 1 1 1 1 4
