Need help with checkbox values in report 1

[paxelius]

Pretext:

My database consists of 2 tables and 1 form with a subform.
The table linked to the subform has 25 x 6 (=150!) fields with datatype yes/no. In the subform I have made 25 lines of questions, each with 6 buttons where the user can check/uncheck a corresponding checkbox as an answer. All this works well, although maybe I've done things alot more difficult than needed ? (Should be possible to make an array, instead of operating with 150 fields of checkboxes).

My objective:

I need to make an extensive report on the corelation of data. Thus I need a way to count how many checkboxes are checked for a certain question and add them up. When I display a report on my data, it only shows the checkboxes - But, I do not want checkboxes to show on the report, only the derived numbers and percentages of their occurances.

Problem:

I don't know where to start..
I've read other posts on this subject where the expression:
=sum(Abs(Field)) seems to be the answer, but I don't understand how to implement this for my report.

How can I translate my checkboxes into values and then work statistical data analysis on their corelation in the report?

Thanks in advance for your help!

 

[paxelius]

I forgot to mention that I am using MS Access 2000.. and that I am desperatly in need of urgent help on this matter.

Thanks.

 

[dhookom]

It sounds like you are "committing spreadsheet". You might want to check out the structure of "At Your Survey" which can be downloaded from

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This survey uses more normalized tables and includes a report of percentages and even one that create pie charts for each question.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[paxelius]

Thanks for the link!

It would be helpfull if you can explain what is meant by "committing spreadsheet" ?

- Now I'm off to study your code...

 

[dhookom]

Committing spreadsheet suggests your table structure is not normalized. Your table resembles an Excel spreadsheet rather than a relational database. I have created thousands of tables over the last 20 years and very few have had more than 20 fields.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[paxelius]

You said: I have created thousands of tables over the last 20 years and very few have had more than 20 fields.

That's encouraging to hear, I knew that what I was doing was 'bad', but it was the only way I managed to get it to work. But I'm capable of learning.

More comments on your code tomorrow...

Kind regards,
Paxelius

 

[paxelius]

Hello dhookom: I've been looking over your survey-code. It's very impressive work, I might be able to learn alot from it. But - I have build my database quite differently, and time does not allow me to deviate from the path I've already taken, I need to finish my base according to the data I already have. And I am using 150 checkbox-fields, and your survey did not deal with how to sum up checkbox values.

I'd be happy if you could lend a hand with my immidiate problem, it is too late for me to try to normalize my tables this time. I only need a working (preferable easy) technique for converting checkboxes into values on the report and thus make statistical data analysis.

If anybody would like to help, I could send you a .zip of my complete database - How about that ?

regards,
Paxelius

 

[dhookom]

Check boxes are values. Each check box has a value of -1. Boxes that are not checked have a value of 0. All you have to do is sum the check boxes and then use Abs() to make them positive numbers.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Crauck]

Thanks dhookom!!

I was surfing Tek-Tips looking for info on How Access recognizes the check boxes so I could do expressions with them. Your post helped me greatly.

Cristy