Displaying values that are not in query results

[vicky464]

I have a query that looks at survey information and tallies how many people voted for in each of 5 answer categories (Highly Satisfied, Satisfied, Neutral, Unsatisfied and Highly Unsatisfied). Sometimes, depending on the question, there are no votes in that category, so only the categories that are voted for display. This makes each query inconsistent from the previous. Is there a way to have all of the possible answers display - even if no one voted? I dont think I could just add additional records to the underlying table, because I need to run some calcs on these values and that would skew the results.

Ultimately I have to create reports, so if it is easier to do this on the report side, please advise.

Thanks.
Vicky

[pkstormy]

Hey Vicky,

Sounds like a function (in a module) which would return a value based upon what is passed to it would be the best approach for this.

ex:
Function retSomeValue(varPassedToFunction as variant) as variant

and then you could set the ControlSource of a field on the report (or as an expression in a query) to something like

=RetSomeValue([MyFieldName])

or call it within code
SomeVariable = RetSomeValue([me!MyFieldOnTheForm])

[ajetrumpet]

have you tried using a left join, vicky?

[vicky464]

Adam,
There is only one table and one field, displayed twice, with a total row - Group by and Count.

[ajetrumpet]

Adam,
There is only one table and one field, displayed twice, with a total row - Group by and Count.

is this solved yet Vicky? Stormy is probably right. Although you might not even need coding at all. Why don't you post up the table, or a small sample of what it looks like?

Hard to get a handle on what you're up against just by reading the words so far.

[vicky464]

Hi Adam,

Okay, here is the table. There are usually only 2 or 3 satisfaction scores (5, 4, 3). 2 and 1 are used infrequently. There is also the occassional blank or N/A. I would like to have a category for each of these results listed in the report. Ultimately, I want something like this


Score Num or Ratings Percentage of total
5 2 66.6%
4 1 33.3%
3 0
2 0
1 0


Here is part of the data table:
Product_Line Project datercvd ABC Learning delivered a quality learning product ABC Learning delivered the learning products within the cost es ABC Learning adhered to the approved project schedule
PSO Billing Guide Project 11/9/2010 4 4 5
Accounts Services Web Interactive Guide 8/4/2010 4 5 5
Accounts Services Automation Help Project 7/22/2010 4 4 4
Clearance CSR Webcast 7/19/2010 4 4 4
Discovery Dashboard Project 4/21/2010 4 4 4
Accounts Services Help Updates 8/4/2010 5 5 5
Fiduciary Trial Project 3/17/2010 3 3 3
Fiduciary Trial Project 3/18/2010 3 3 #N/A
Fiduciary Trial Project 3/19/2010 4 #N/A 5
Fiduciary Trial Project 3/20/2010 5 4 4

[ajetrumpet]

do you want that end result grouped by project of project type? Isn't that what you said before?

I don't know if you can get that output from a crosstab query, and if you can't you might have to make a new table. Either way, once you do that, you can dcount() the 3 fields and sum them if you want. That will work I would think. The same thing with counting the values.

Does that make sense? That's the way I see it anyway.

[vicky464]

Grouping would be by Product Line, or Overall. There are 8 questions, all of them with the same rating scale. I would prefer to do one query that displays all the questions, but so far I have only been able to do it with individual queries.

The initial question was about showing the ratings that were not part of the results. So that I would see the 2's and 1's, even if there were no 2's or 1's for that particular question.

Off to try the crosstab query. Been using Pivot Tables (I am more comfortable with Excel) but cant seem to get it with PT's.

Vicky

[ajetrumpet]

vicky,

if the 2's and 1's are in different columns, which I assume they are, put them in the select statement, and use:

iif(field is null, 0, field) as fieldname

as the actual field inclusion. that'll take care of that part. I still stand by my opinion. you can't do this all in one shot through the interface, and without code and a semi-long procedure. Access just can't do it.

[pkstormy]

You can also create an expression in a query like:

Results from expressions below:
recID......Field1..........Field2.......MyExpressi on1.........MyExpression2..........MyExpression3
1..............3............ <null>...............3...........................3 ..........................3
2...........<null>............3................... 3...........................3..................... ...<null>
3..............2.............<null>............... 2...........................2..................... .....2
4..............2................2................. .2............................2................... .......2
5............<null>.........<null>............<nul l>.......................0........................ ...0
6............<null>............2.................. 2...........................2..................... ....<null>
7...............4...............3................. .4...........................4.................... .......4

(equations for below expressions)

MyExpression1: iif(isnull([Field1]),[Field2],[Field1])

or even get more complex such as:

MyExpression2: IIf((IsNull([Field1]) And IsNull([Field2])),0,IIf(Not IsNull([Field1]),[Field1],[Field2]))

or

MyExpression3: IIf(IsNull([Field1]),IIf(IsNull([Field2]),0),[Field1])