inaccurate figures when embedding multiple queries

[cochise]

My goal is to have a query that gives me sales, % total sales, call count, % call count, average sale, max sale...etc. grouped by dept.

Here are my queries:
1. qryGrandTotals (sum amount,count of calls)
2. qryCallsDept (groups by dept, count calls)
3. qryDept (groups by dept, sum amount, expression of sum amount/totalamount, count invoice ...etc.

in #3 I try to create a % calls, but seem unable to do it by making an expression that takes sum (calls/totalcalls)

when I use qryCallsDept.calls / qryGrandTotals.calls as a field in #3, I get super inflated inaccurate figures.

I can get the results in a simple query grouping by dept, qyrCallsDept.calls / qryGrandTotals.calls, but whenever I try to integrate it into another query or add other fields the figures go crazy.

Any advice would be appreciated

 

[link9]

If you have all the little queries working, then you can string them together, enclosing each in parentheses and giving the result an alias:

For example, let's say that I have a query that gives me a result such as this:

SELECT SUM(sales) FROM sales

and then I have one that gives me total number of records such as:

SELECT COUNT(salesID) FROM sales

Then average sales would be this:

SELECT(
(SELECT SUM(total) FROM sales)/
(SELECT COUNT(salesID) FROM sales)
)AS averageSales

Which would return me one value

If you had more to select, and the subqueries to go with them, you can just string them together separated by commas. Just be sure to give each of them an alias.

good luck!
Paul Prewett