DCount in a Form

[killermonkey]

Ok I've got yet another question in relations to this thread/database:


https://www.accessforums.net/program...elp-38626.html

I spend all day yesterday trying to come up with a viable solution but it's escaping me. I want to create a form where a user can enter new information while at the same time see a quick overview of how the month is looking. Basically I want multiple textboxes that count stuff like the number of people that score between a 90 and 100 (Excellent), the number that score between a 75 and 89.9 (Satisfactory) and the number of people that score below a 75 (Failing). Additionally I'd like to see total number of passing scores and failing scores, failures in certain categories ect ect...more or less something like this that was created in excel:



The problem I'm having is that dcount blows accesses mind. It cant seem to handle even one dcount without freezing everything.

What I'm wondering is there an alternative to dcount where I can achieve the same thing without the lagging headache?


Thanks in advance

Tyler

[June7]

Options could be:

1. listbox with RowSource based on aggregate query

2. subform with RecordSource based on aggregate query

[killermonkey]

June,

Would I have to build a separate aggregate query for every criteria I was trying to find?

[June7]

I see possibly 5: Overall, AgeByGender, Scores (what about gender), Category, GenderFailures (this one seems extraneous because the info is in the AgeByGender failures). The SQL could probably be built right in the RowSource or RecordSource property.

Won't be able to do color coding in listbox nor the centered text (not without some fancy code).

See data discrepancy: 48 failures by gender but 49 failures by category.

I know domain aggregate functions can be slow but I use them frequently without major issues.

[killermonkey]

June,

I have a couple follow up questions. I got the listboxes to work for overall, gender failures, and score range failures. I having trouble figuring out how to combine the category failures into one listbox since they are separate columns in the query. Also I have the listboxes set up so that they only show data month that is selected off a combobox on the form. What's happening is that when I change the month it requeries the listboxes but sometimes only the first item in the list will display. You have any idea why it would be doing that?

[June7]

Don't understand 'combine the category failures into one listbox since they are separate columns'. Why would separate columns be an issue? Of course data is in separate columns.

No idea why the requery shows only single item in listbox.