Using MS Access/Access 2010 Reports

Advertisement


Question
QUESTION: Hi Scott
I have not had to call on you for assistance for some time now.
In my database I have relevant tables and queries set up to provide a report.
The report is set up to show all members (grouped) in the database who have attended specific activities (as a sub group) over a period of time. I have created the join properties to include those members who have not attended any activities also.
All works well so far.
I have included a count field in the sub group header to summarize the number of activities participated in by each member.
My problem is that for members who have participated in at least one activity the activity is shown with the correct count; however, if a member has not participated in any activity, the member's name is shown, no activity is shown, but a count of "1" is shown.
I hope this makes sense to you?
What I want the report to show for those members who have not participated is simply their name.
Can you help please?
Thanks
John

ANSWER: What's the expression in the Count control?

The probable reason is since you are still showing a record for that person the record is counted, hence the 1. A possible solution would be to add a column in your query:

Counter: IIF(IsNull([Activity]),0,1)

This column should show a 0 for any record where there was no activity and 1 if there was. You could then SUM this field in your report (instead of a count) and that should give an accurate count.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott
Thanks for your speedy response.
I was using Count(*). After giving it some further thought however I subsequently changed this to Count([Activity]) and it seems to have solved the problem.
Would your advice be to leave it at that, or take up your suggestion?
Thanks
John

Answer
That's why I asked what expression you were using. If you were already using Count([Activity]) then you would have had to use my suggestion. But if Count([Activity]) works, then just leave it.

Count(*) would count all the records in the group and since you have a record for each person, then it would return a 1.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.