I created two queries against my ACTIVITY table:
“qryActivity0”
SELECT Activity.Zip, Activity.Cat, Activity.SubCat, Activity.Office, Activity.Date
FROM Activity
ORDER BY Activity.Zip;
“qryActivity1”
SELECT qryActivity0.Zip, Count(*) AS CountOfZips
FROM qryActivity0
WHERE (((qryActivity0.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
ORDER BY CountOfZips;
When I try to ORDER BY CountOfZips, I get an error message: “You tried to execute a query that does not include the specified expression ‘Zip’ as part of an aggregate function.” NOTE: in “qryActivity1” when I replace the ORDER BY statement with "GROUP BY qryActivity0.Zip;", I obtain correct results but the results are sorted by Zip and not by CountOfZip. CountOfZip sorting would enable me to SELECT TOP 3 Zips in a subsequent query.
Question 1: What coding is required to create output from “qryActivity1” that is sorted in Descending order on CountOfZips? Is it even possible?
Related Item: When I try to SELECT any additional “qryActivity0” fields (e.g., ‘qryActivity0.Cat’) in the SELECT statement of “qryActivity1”, I get an error message: “You tried to execute a query that does not include the specified expression ‘Cat’ as part of an aggregate function.”
Question 2: What coding is required to SELECT these additional fields in “qryActivity1” or is this simply not possible?
Thank you,
-bill
“qryActivity0”
SELECT Activity.Zip, Activity.Cat, Activity.SubCat, Activity.Office, Activity.Date
FROM Activity
ORDER BY Activity.Zip;
“qryActivity1”
SELECT qryActivity0.Zip, Count(*) AS CountOfZips
FROM qryActivity0
WHERE (((qryActivity0.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
ORDER BY CountOfZips;
When I try to ORDER BY CountOfZips, I get an error message: “You tried to execute a query that does not include the specified expression ‘Zip’ as part of an aggregate function.” NOTE: in “qryActivity1” when I replace the ORDER BY statement with "GROUP BY qryActivity0.Zip;", I obtain correct results but the results are sorted by Zip and not by CountOfZip. CountOfZip sorting would enable me to SELECT TOP 3 Zips in a subsequent query.
Question 1: What coding is required to create output from “qryActivity1” that is sorted in Descending order on CountOfZips? Is it even possible?
Related Item: When I try to SELECT any additional “qryActivity0” fields (e.g., ‘qryActivity0.Cat’) in the SELECT statement of “qryActivity1”, I get an error message: “You tried to execute a query that does not include the specified expression ‘Cat’ as part of an aggregate function.”
Question 2: What coding is required to SELECT these additional fields in “qryActivity1” or is this simply not possible?
Thank you,
-bill