Results 1 to 2 of 2
2003-07-11, 20:08 #1cgschlangenGuest
Displaying Zeros in Reports (Access 2000)
I'm sure you've been asked this several times, as it seems like such a mundane question... but I'm trying to tally up yes/no boxes for a report. I may have gone about it all wrong so let me first explain what I've done... I've attached a sample database - so I'll just explain what I did to get where I am w/ it.
First I created a table - YesNoTable - with 2 checkbox (yes/no) fields - I then put in two records (1 yes and 1 no)
Next I created a query titled YesQuery (using the query wizard) which would query the 'Yes' column of my table - I clicked on the summary options button and selected 'count records in YesNoTable' once finished with the wizard I modified the design of the query w/ a =True statement in the criteria (so it would only return boxes with the check)
I followed the same process creating a NoQuery query which would query the 'No' column of the table...
To tie them together for my report I created another query which would pool the "Count Of YesNoTable: Count(*)" column of both queries and return two simple colums for a recordsource for my report...
However (you saw this coming didn't you?) It will only return numbers if there is at least one instance of Yes and No being checked... So I find myself in this conundrum and I turn to you for a way out...
Any thoughts/suggestions/solutions will be appreciated [img]/forums/images/smilies/tongue.gif[/img]
2003-07-11, 20:22 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Displaying Zeros in Reports (Access 2000)
You can count the number of Yes and No's in one query. Here is the SQL of the query:
SELECT Abs(Sum([Yes])) AS CountOfYes, Abs(Sum([No])) AS CountOfNo
You create this query by creating a new query based on YesNoTable in design view. The definition of the two fields is CountOfYes: Abs(Sum([Yes])) and CountOfNo: Abs(Sum([No])).