Create a report with total line showing group totals 1

[cfvcf]

I'm not sure how to approach how to create a report that provides total counts and percentages of a group of records for 1 person. Example: A record has an "employee id", and an identifier for the "quality of referral" for a particular loan. So I need to create a report that lists by "Employee ID", the number of "Good Referrals" or "Bad Referrals" then "Total Referrals" then "Percentages" of each. I am using a detail table that contains all the information I need. At first I thought it would be simple, but I'm struggling. I'd appreciate any help! Thanks!

 

[Remou]

Do you want:
[tt]
Good Bad Total %Good
Joe 90 10 100 90[/tt]

If so, basing the report on a query would be best.

 

[cfvcf]

Something strange is happening. I have 3 queries. One is a query that is created for all records that have "good" referrals, and that query contains EMP, Last, First, a text field with "Good Referral", and a field with the total count of records that employee has for "Good Referrals".

Then I created another query for "Bad Referrals" and another one for "Incomplete Referrals" with the same fields only the "type of referral" is different. I then created another query and added those 3 queries and created a link between their EMP ID. I only want 1 record for each employee. So in the new query, I have EMP, Last, First, and 3 fields containing the counts for "Good Referrals", "Bad Referrals", and "Incomplete Referrals".

The problem with the join is that I don't have an option to include rows that may not have a match in one of the the other 2 queries. So I don't know what to set as the Join Properties. Selecting #1 "Only includes rows where the joined fields from both tables are equal". The other two options don't work either.

Does anyone have an idea of how I can include "All" records with their counts but not create 2 duplicate records for the EMP, Last, and First?

 

[Remou]

Please post the SQL for the queries.

Have you seen:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

 

[cfvcf]

Below is bringing in the 3 queries:
SELECT DISTINCTROW qrySelect_Good_Referrals_by_NBK.Standard_ID,
qrySelect_Good_Referrals_by_NBK.Associate_Last_Name, qrySelect_Good_Referrals_by_NBK.Associate_First_Name, qrySelect_Good_Referrals_by_NBK.CountOfReferral_Quality, qrySelect_Rejected_Incomplete_by_NBK.CountOfReferral_Quality, qrySelect_Incomplete_With_Rework_by_NBK.CountOfReferral_Quality
FROM (qrySelect_Good_Referrals_by_NBK LEFT JOIN
qrySelect_Rejected_Incomplete_by_NBK ON qrySelect_Good_Referrals_by_NBK.Standard_ID = qrySelect_Rejected_Incomplete_by_NBK.Standard_ID) LEFT JOIN qrySelect_Incomplete_With_Rework_by_NBK ON qrySelect_Good_Referrals_by_NBK.Standard_ID = qrySelect_Incomplete_With_Rework_by_NBK.Standard_ID
GROUP BY qrySelect_Good_Referrals_by_NBK.Standard_ID,
qrySelect_Good_Referrals_by_NBK.Associate_Last_Name, qrySelect_Good_Referrals_by_NBK.Associate_First_Name, qrySelect_Good_Referrals_by_NBK.CountOfReferral_Quality, qrySelect_Rejected_Incomplete_by_NBK.CountOfReferral_Quality, qrySelect_Incomplete_With_Rework_by_NBK.CountOfReferral_Quality;

Below is from one of the 3 queries:

SELECT BOA_Personnel_List.Standard_ID,
BOA_Personnel_List.Associate_Last_Name, BOA_Personnel_List.Associate_First_Name, Fraud_Event_Table.Referral_Quality, Count(Fraud_Event_Table.Referral_Quality) AS CountOfReferral_Quality
FROM BOA_Personnel_List INNER JOIN Fraud_Event_Table ON BOA_Personnel_List.Standard_ID=Fraud_Event_Table.Submitted_BY_NBK
GROUP BY BOA_Personnel_List.Standard_ID, BOA_Personnel_List.Associate_Last_Name, BOA_Personnel_List.Associate_First_Name, Fraud_Event_Table.Referral_Quality, BOA_Personnel_List.DropdownActive
HAVING (((Fraud_Event_Table.Referral_Quality)="Good Referral") AND ((BOA_Personnel_List.DropdownActive)=True));

Would a make table query be what I need?
Thanks!

 

[Remou]

Perhaps you want something like:

SELECT B.Standard_ID, B.Associate_Last_Name, B.Associate_First_Name, B.DropdownActive, (Select Count(*) From Fraud_Event_Table A Where A.Submitted_BY_NBK=B.Standard_ID AND Referral_Quality="Good Referral") AS Good, (Select Count(*) From Fraud_Event_Table A Where A.Submitted_BY_NBK=B.Standard_ID AND Referral_Quality="Rejected Incomplete") AS Rejected, (Select Count(*) From Fraud_Event_Table A Where A.Submitted_BY_NBK=B.Standard_ID AND Referral_Quality="Incomplete With Rework") AS Incomplete
FROM BOA_Personnel_List AS B
WHERE (((B.DropdownActive)=True));

 

[cfvcf]

Not to sound dense, but are you replacing the 2nd set of code I cut & pasted with this?

 

[Remou]

Hopefully, it will replace all the queries.

 

[cfvcf]

You are absolutely right! Thanks so much!