In need of an expert

[betheball]

I have the following query that tells me how many times each option of a particular field (Age_Causes.Cause) appears in a table (AgeReview) based on certain criteria (ReviewDate, Operation, Department and TeamIDRS):

SELECT Age_Causes.Cause, Count(AgeReview.AgeCause) AS CountOfAgeCause2
FROM Age_Causes INNER JOIN (Team_Info INNER JOIN AgeReview ON Team_Info.TeamIDRS = AgeReview.TeamIDRS) ON Age_Causes.ID = AgeReview.AgeCause
WHERE (((AgeReview.ReviewDate)=[ReviewDate1]) AND ((Team_Infperation) Like [Operation1]) AND ((Team_Info.Department) Like [Department1]) AND ((AgeReview.TeamIDRS) Like [TeamIDRS1]))
GROUP BY Age_Causes.Cause;

I then have this query that tells me how many total records there are based on the same criteria as the 1st query:

SELECT Age_Causes.Cause, Count(AgeReview.AgeCause) AS CountOfAgeCause2
FROM Age_Causes INNER JOIN (Team_Info INNER JOIN AgeReview ON Team_Info.TeamIDRS = AgeReview.TeamIDRS) ON Age_Causes.ID = AgeReview.AgeCause
WHERE (((AgeReview.ReviewDate)=[ReviewDate1]) AND ((Team_Infperation) Like [Operation1]) AND ((Team_Info.Department) Like [Department1]) AND ((AgeReview.TeamIDRS) Like [TeamIDRS1]))
GROUP BY Age_Causes.Cause;

Finally, I have this third query that divides the results in the first query by the results of the second to give the percentage for each value in the aforementioned table:

SELECT Age_Cause_Summary_by_date_Team.Cause, Age_Cause_Summary_by_date_Team.CountOfAgeCause2, Format([Age_Cause_Summary_by_date_Team]![CountOfAgeCause2]/[Cause_Summary_team]![CountOfCause],"Percent") AS [Percent]
FROM Age_Cause_Summary_by_date_Team, Cause_Summary_team
ORDER BY Format([Age_Cause_Summary_by_date_Team]![CountOfAgeCause2]/[Cause_Summary_team]![CountOfCause],"Percent") DESC;

If at all possible, I need one query that will do all of the above. I am trying to accomplish the same result as the third query on a webpage using FrontPage, but using a query that is based on 2 other queries is causing me to lose my parameters from the 1st two queries.

 

[normajean]

Hi: Hope this will help; it looked to me like both subqueries in your question were Identical! It sounded from your description that what you were looking for in the second query was a total of all records, so I just took the 'Group By' out of the second query, so you would get a total count of All records (and not a set of counts by Causes). That being said, take a good look at the second query (B) below, and make sure its what you intended.
I wasn't able to debug, because I don't actually have your tables, but this should work; let me know.
Normajean

SELECT A.Cause, A.CountOfCause, B.CountOfAllCauses
Format(A.CountOfCause/B.CountOfAllCauses,"Percent") AS [Percent]
FROM
(SELECT Age_Causes.Cause AS Cause, Count(AgeReview.AgeCause) AS CountOfCause FROM Age_Causes INNER JOIN (Team_Info INNER JOIN AgeReview ON Team_Info.TeamIDRS = AgeReview.TeamIDRS) ON Age_Causes.ID = AgeReview.AgeCause WHERE (((AgeReview.ReviewDate)=[ReviewDate1]) AND ((Team_Infperation) Like [Operation1]) AND ((Team_Info.Department) Like [Department1]) AND ((AgeReview.TeamIDRS) Like [TeamIDRS1]))
GROUP BY Age_Causes.Cause A,
(SELECT Count(AgeReview.AgeCause) AS CountOfAllCauses
FROM Age_Causes INNER JOIN (Team_Info INNER JOIN AgeReview ON Team_Info.TeamIDRS = AgeReview.TeamIDRS) ON Age_Causes.ID = AgeReview.AgeCause WHERE ((AgeReview.ReviewDate=[ReviewDate1]) AND (Team_Infperation Like [Operation1]) AND (Team_Info.Department Like [Department1]) AND (AgeReview.TeamIDRS Like [TeamIDRS1])) B
ORDER BY Format(A.CountOfCause/B.CountOfAllCauses,"Percent") DESC;

 

[normajean]

Just replied, and don't know why a smily face was substitued in place of the ending parenthesis prior to query 1 nametag 'A' and prior to query 2 nametag 'B' - Please substitute an ending parens for these. Normajean

 

[betheball]

Thank you NormaJean. The smilies were appropriate because I am now all smiles. All last night I had visions of SQL statements going through my head because I couldn't get this to work. Your code worked flawlessly. Thank you so much.

BTW, yes the first two examples in my post are identical. I guess I am a little copy and paste challenged.