Tina,
Thanks so much once again!...Here is what I came up with from your
instructions and some books and help files. I am not certain I understood
all of your directions but it did get me to making these queries below and I
got what I wanted. I used the query grid of course and not SQL but I think
the SQL makes more sense to you guys than my written explanations.
My First Query...
SELECT qryEmpNameLastFirst.strEmployeeID, qryEmpNameLastFirst.Employee,
tblAudits.AuditName, Count(tblEmployeeAudits.EmpAuditID) AS
CountOfEmpAuditID
FROM tblAudits RIGHT JOIN (qryEmpNameLastFirst LEFT JOIN tblEmployeeAudits
ON qryEmpNameLastFirst.strEmployeeID = tblEmployeeAudits.EmpAudit_fkEmpID)
ON tblAudits.AuditID = tblEmployeeAudits.EmpAudit_fkAuditID
WHERE (((tblEmployeeAudits.EmpAuditDate) Between [Start Date] And [End
Date]))
GROUP BY qryEmpNameLastFirst.strEmployeeID, qryEmpNameLastFirst.Employee,
tblAudits.AuditName
ORDER BY qryEmpNameLastFirst.Employee;
My Second Query...
SELECT qryEmpNameLastFirst.strEmployeeID, qryEmpNameLastFirst.Employee,
tblAudits.AuditName, tblEmployeeAudits.EmpAuditID,
tblEmployeeAudits.EmpAuditDate
FROM tblAudits RIGHT JOIN (qryEmpNameLastFirst LEFT JOIN tblEmployeeAudits
ON qryEmpNameLastFirst.strEmployeeID = tblEmployeeAudits.EmpAudit_fkEmpID)
ON tblAudits.AuditID = tblEmployeeAudits.EmpAudit_fkAuditID
GROUP BY qryEmpNameLastFirst.strEmployeeID, qryEmpNameLastFirst.Employee,
tblAudits.AuditName, tblEmployeeAudits.EmpAuditID,
tblEmployeeAudits.EmpAuditDate;
My Final Query that joins to first two...
SELECT DISTINCT qryTherapistAuditNumbersAllRecords.Employee,
qryTherapistAuditNumbersAllRecords.AuditName, Nz([CountOfEmpAuditID],0) AS
AuditCount
FROM qryTherapistAuditNumbersAllRecords LEFT JOIN
qryrptTherapistAuditNumbersByDate ON
qryTherapistAuditNumbersAllRecords.strEmployeeID =
qryrptTherapistAuditNumbersByDate.strEmployeeID;
Now the Crosstab Query is based on the Final query which returns the
AuditName across the top and all the Employees Names down on the left even
if they didn't have any audits done yet. It shows the number done for each
person, the employees who have not had any audits done for a given AuditName
have zero. The auditor takes this sheet out with them to determine who
needs audits done. The goal is to have 6 audits done for each AuditName per
employee.
tina said:
well, if i understood correctly what you're trying to do - you might be
able
to do it with two queries.
name of first query is "qryEmpAuditCount":
***** BEGIN AIR CODE *****
SELECT tblEmployeeAudits.EmpAudit_fkEmpID, tblAudits.AuditName,
Count(tblEmployeeAudits.EmpAuditID) AS AuditCount,
tblEmployeeAudits.EmpAuditDate FROM tblEmployeeAudits LEFT JOIN tblAudits
ON
tblEmployeeAudits. EmpAudit_fkAuditID = tblAudits.AuditID GROUP BY
tblEmployeeAudits.EmpAudit_fkEmpID, tblAudits.AuditName,
tblEmployeeAudits.EmpAuditDate HAVING tblEmployeeAudits.EmpAuditDate
Between
[Start Date] And [End Date];
second query is based on the first query, and your employees table - which
i
gave the following table and field names:
tblEmployees
EmpID (pk)
FirstName
LastName
you'll need to substitute the correct names, of course.
SELECT tblEmployees.LastName & ", " & tblEmployees.FirstName AS Employee,
qryEmployeeAuditCount.AuditName, qryEmployeeAuditCount.AuditCount,
qryEmployeeAuditCount.EmpAuditDate FROM tblEmployees LEFT JOIN
qryEmployeeAuditCount ON tblEmployees.EmpID =
qryEmployeeAuditCount.EmpAudit_fkEmpID ORDER BY tblEmployees.LastName,
tblEmployees.FirstName;
***** END AIR CODE *****
the idea is that the first query is based on tblEmpAudits, with tblAudits
linked in merely to provide the AuditName. the query pulls all the info
about the employee audits, filtered by the starting and ending date
parameters, and grouped by employee ID, audit name, and audit date. the
second query is based on tblEmployees, with a LEFT JOIN link to the first
query on the employee ID field. all employees are listed from the table
using a concatenated field for "LastName, FirstName", with the audit name,
audit count, and audit date showing for the matching records from the
first
query, and all records sorted by LastName and then FirstName (which do not
show in the dataset as separate fields).
hth
LMB said:
GEE, another query...hummph. All these queries to get other queries are
getting me all confused <g> My database is going to be huge just from
all
the descriptions I am having to create.
Thanks,
Linda
In that case you might need to resort to three queries.
Keep your existing query
Create a new query that returns a master list of all IDs that you
want
in
your final list, and a third query that joins these two together using
a
left
join to return all the rows from your master list. You may need to
use
an
iif
or nz function to fill in a zero in place of the nulls
Chris
:
That was interesting. It pulled out the records of people who were
filtered
out in one of the base queries but people who were supposed to show in
the
query with a 0 still didn't show.
Linda
