If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Show count if zero
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" wrote in message ... 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" wrote in message ... 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 "ChrisJ" wrote in message ... 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 "LMB" wrote: 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 |
#2
|
|||
|
|||
hurray, you got it figured out and got just what you needed - good job
Linda! thanks for posting back to let me know how things turned out. "LMB" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 "ChrisJ" wrote in message ... 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 "LMB" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count Position of Filtered TEXT cells in a column | Sam via OfficeKB.com | Worksheet Functions | 8 | May 18th, 2005 04:23 AM |
Count Position of Filtered TEXT cells in a column | Sam via OfficeKB.com | Worksheet Functions | 0 | May 15th, 2005 08:14 PM |
Calendar Question | Josh | General Discussion | 7 | March 28th, 2005 11:19 PM |
UDF and Calculation tree | Ken Wright | Links and Linking | 1 | February 6th, 2005 04:58 PM |
Power Show Problems | Fran McConville | Powerpoint | 3 | October 18th, 2004 02:52 AM |