Count, Sum, Aggregate Questions

[elinorigb]

I am not sure how to title this question; I've found a way to do this, but I'm wondering if there's a better way. I've developed a database from a survey taken where I work. Many of the questions involved a drop down menu, ie. "How often do you order supplies?" with choices such as once a day, once a week, never, etc.

Now I would like to combine some details of the survey. For example, I would like to query how often employees ask for reimbursements, but ONLY for those employees who ORDER SUPPLIES. In this case, I don't care about how OFTEN they order supplies.

This is how I've done it. Query1, below, simply groups employees who order supplies according to how often they do it, including those that NEVER order supplies:

SELECT Count(*) AS Supplies, tblSurvey.Supplies
FROM tblSurvey
GROUP BY tblSurvey.Supplies
ORDER BY Count(*) DESC;

Query2 eliminates the people who DON'T order supplies. This query seems unnecessary, but I found it was one way I could eventually get a SUM (though I won't discuss that on this post):

SELECT Count(*) AS CountofSupplies, tblSurvey.Supplies, tblSurvey.ID
FROM tblSurvey
GROUP BY tblSurvey.Supplies, tblSurvey.ID
HAVING (((tblSurvey.Supplies) Is Not Null And (tblSurvey.Supplies)<>"Never"))
ORDER BY Count(*) DESC;

Query3 joins Query2 to the survey table, in order to determine HOW OFTEN employees are reimbursed but ONLY for those employees who DO order supplies.

SELECT Count(tblSurvey.Reimburse) AS CountofReimburse,
tblSurvey.Reimburse
FROM tblSurvey INNER JOIN Query2 ON tblSurvey.ID = Query2.ID
GROUP BY tblSurvey.Reimburse;

I find that this works for me, but I'm wondering if there's a better more efficient way to do this that requires less steps? But I may be too much of a novice to do this in any other way. Remember, since I don't care about how often employees order supplies, I've tried to eliminate that grouping, which is why this querying process seems to have extra steps.

thanks!

 

[jrbarnett]

Hi

If you trying to remove the people who have not ordered supplies from the second query, it can be done with a small change:

SELECT Count(*) AS CountofSupplies, tblSurvey.Supplies, tblSurvey.ID
FROM tblSurvey
WHERE (((tblSurvey.Supplies) Is Not Null And (tblSurvey.Supplies)<>"Never"))
GROUP BY tblSurvey.Supplies, tblSurvey.ID
ORDER BY Count(*) DESC;

Your query looks like it was generated by the Access query builder. Nothing wrong with that - but there is a bug in it, it moves all query criteria to a Having clause if the totals window is shown. With large amounts of data this can slow down the data recovery, because a WHERE clause is used to restrict the data retrieved from the table, and therefore the grouping is only carried out on that data, whereas in your query the group by was done on all data in the table whether there were orders or not.

John

 

[elinorigb]

Thanks for that feedback. I'll keep that bug in mind.