Keeping a static number of fields

[MisterCrash]

I have a database of people, and I run a query that groups people by age and counts the number of people that are at a particular age. The results of this are then pasted into an Excel spreadsheet, which performs various calculations based on the age.

However, some age groups are not represented in the database (for example, there are no 61-year-olds), and the output from Access has no entry for these ages. I would greatly prefer it if Age 61 did show up in the query result, but would have a value of zero (otherwise, I have to reconfigure the Excel sheet every time the last person leaves an age group or the first person goes into a previously vacant age group).

The solution I have been using is to create a dummy table (with the field [TableAge]) that lists the range of ages I'm interested in, then calculates the number of people of that age by:

=IIF([Age]=[TableAge], [CountOfMemberID], 0)

However, this seems computationally cumbersome, and I wondered if Access had a simpler means of accomplishing this feat. Thank you!

 

[Dugantrain]

Try this: Before you get to the Counting query, write a query which will capture all the ages record by record and set up your iif criteria there: iif([yourtable].[age]>youragecriteria,0,[yourtable].[age])

So what you're saying here is "If the age is greater than a certain criteria, then return a 0, else return the original number". Then, Count off of this query in your second query. Of course, the more criteria that you have, the heavier your iif statement will be, but either way it should accomplish your task.