Summing values across a grooup of fields in each record

[rmcintire]

I have a table with a group of fields representing the result of a list of audit questions, with the answers stored as numbers 1,2 or 3 for yes, no, n/a. On a tip from this site I'm using a sum function as follows:

=Sum(IIf(1 In ([Rx1],[Rx2],[Rx3],[Rx4],[Rx4a],[Rx4b],[Rx4c],[Prac_notes1],
[Prac_notes1a],[Prac_notes1b],[Prac_notes1c],[Prac_notes2],[Prac_notes3],
[Prac_notes4],[Delivery1],[Delivery2],[Delivery3],[Dates1],[Dates2],
[Billing1],[Sup_stds1],[Adv_ben_notice1],[Residence1],[Facil_billing1],
[Facil_billing2],[Bill_device_not_del],[Patient_info1],[Insur_verif1],
[Insur_verif2],[Insur_verif3],[Insur_verif4],[HIPPA],[Chart_org1],
[Finance1],[Finance2],[Finance3],[Finance4]),1,0))


I have this in the controlsource of an unbound textbox on a report. The report is based on a query that includes all of these fields. I am not getting any syntax errors, but when I run the report it prompts me for parameters for each field listed within the expression one at a time. I'n not all that current on VB coding. Can anyone tell me where this has gone awry?

Bob

 

[dhookom]

Are you totally set on your table structure? It isn't normalized. I would have created a table where each each audit question and its number creates a record rather than storing values as field names.

If you can't change the structure, consider normalizing your table with a union query.

SELECT PKFieldName,"Rx1" as Question, Rx1 as TheValue
FROM tblAudit
UNION ALL
SELECT PKFieldName,"Rx2", Rx2
FROM tblAudit
UNION ALL
--- etc ---

Summing or averaging results then becomes quite simple with a totals query.


Duane
Hook'D on Access
MS Access MVP

 

[rmcintire]

THanks for your reply, dhookum, but that would require a complete redesign of a db / app, which I inherited. That's not doable.

I think I'm pretty close to a workable solution with that Sum ( iif ( 1 in field list )function, but can't determine why it's prompting for a parameter value for each field in turn when the report is run. Again, the report is based on a query that includes all of the fields in the sum function.

Thanks, Bob

 

[fneily]

The syntax for the IN function is:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or characters.

So based on the defintion, and if you spelled your fieldnames correctly, it's not recognizing them as values. You can first create a string by concatenating the values of the fields then use that string variable in your test.

And, as mentioned, your table is not normalized. "That's not doable." It can be normalized if you so desired.

 

[MichaelRed]

There are a number of functions in these fora (Tek-Tips) which illustrate (& demonstrate) an easier approach, I believe one of them is (something like) basRowSum, or basArray*. All of the 'row set aggregates' use a parameter array, so another search tool is 'paramarray', which MUST be declared as variant.



MichaelRed

 

[rmcintire]

Thanks fneily. I am familiar with the use of the IN qaulifier in a Select stmt. I first attempted this as a select stmt, but received errors about about the expression. I was advised to use it in the aforementioned function stmt in an unbound text box on the report. This seems like a clean, simple solution without having to make structural design changes to a database that can't be cost justified. The function does not report syntax errors, but as you mentioned it does not appear to recognize the fields listed in the inner parens as values. Is this simply an MS Access function syntax issue?

Bob

 

[dhookom]

You don't have to make structural design changes to your database if you use a normalizing union query.

Duane
Hook'D on Access
MS Access MVP