Access aggregate query don't group by

etech0
etech0 used Ask the Experts™
on
I have an aggregate query in access. When viewing the query in datasheet view, it does not group by a specific column (which does not even appear). Rather, I have two separate calculated fields that contain iifs based on that field, and show the sum of that value. However, in my continuous form that is based on that query, I get two lines, one for each possible value of my field. Why is that, and how can I fix it?

queryform
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
what is the record source of your form?

upload a  copy of the db..(remove sensitive info)
"I have an aggregate query in access"

Is this actually a Crosstab query rather than a simple Totals query?

That could produce the results you see in the form?

Author

Commented:
'Select' and 'Totals' are selected on top.

Here is my query:
SELECT Personnel.Username, Sum(IIf([copywriter]='DB',1,0)) AS PendingDB, Sum(IIf([copywriter]='ES',1,0)) AS PendingES
FROM Personnel INNER JOIN [CatWeb Work] ON Personnel.PersonID = [CatWeb Work].ProdSpecialist
WHERE ((([CatWeb Work].[Copy Recd])=False) AND (([CatWeb Work].ProdSpecialist) Is Not Null) AND (([CatWeb Work].[Copy Req])=Yes) AND (([CatWeb Work].status)<>"canceled"))
GROUP BY Personnel.Username;

See attached sample database file.
Sample.accdb
The form works fine here.  Records are exactly as expected.
Capture5.JPG

Author

Commented:
Funny - it works fine on my end too now. Don't know what did it, but glad it's resolved!
Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial