Percentage Calculation In A Query

[ccocom]

I already posted this under reports and was directed to queries; sorry in advance for reposting, but this post will be more specific.

I have a table with one field for each of 75+ survey questions. All survey responses are either 1, 2, 3, 4 or 5. Ultimately, I need a report that will tell me what percentage of respondants answered 1 (or 2 or 3 or 4 or 5) to each question.

I have dabbled with crosstab queries, but because the respondants are anonymous and do not need to be categorized in any way, I don't think a crosstab query is the best bet.

I have also tried group by queries, but I can not figure out how to separate out the responses by 1, 2, 3, 4, and 5.

Would I be better off exporting the table to an excel doc and doing my calcs there?

If not, in addition to my general lack of understanding as to approach this problem, I do not know how to best write an expression that will calculate a percentage (and be formatted as such).

Please help.

 

[GumbyD]

Wow - what a cool problem! I would like to write some code (which I think is the best way to handle this) and have started on some ideas, but I think it might be better if I had your database to work with so when I finish you can actually use it easily. Can you post you file or at least some screen shots of what the tables and data look like.

GumbyD

 

[ccocom]

I have attached two screen shots as the db is too large to send. Let me know if there is anything else I can send. Thanks so much for your help.

 

[GumbyD]

ccocom -

I looked at the screen shots and have started to design a test table the resembles your table. I have a couple more questions. Do all the question fields start with "IIComp"? Is the Number datatype on the question fields "integer" or "Long integer"? And finally I am guessing your vision for how this would look in the end would be some like:

1 2 3 4 5
IIComp1AcupCurr 5% 15% 35% 25% 20%
IIComp1AcupClinTrain 4% 13% 38% 26% 19%
IIComp1AcupFaculty 1% 3% 12% 74% 10%

etc...

Is that what you have in mind?

GumbyD

 

[GumbyD]

Well I lost all my formatting, hope you get the idea.

GumbyD

 

[ccocom]

Thanks for taking a look at this, GumbyD. The question fields are labeled by survey section (i.e., II), then by competency (i.e., Comp1), and finally by a keyword in the question (i.e., curr=curriculum). The datatype on the question field is long integer (although I am not sure why). Re: my vision; you are right on. This is indeed how I would like it to look.

1 2 3 4 5
IIComp1AcupCurr 5% 15% 35% 25% 20%
IIComp1AcupClinTrain 4% 13% 38% 26% 19%
IIComp1AcupFaculty 1% 3% 12% 74% 10%

Thanks again.

 

[GumbyD]

OK - I have created a working model. It is in a pretty raw format so you may want to take and take some time to make it look pretty. It is not as easy as just putting a function call in a query (at least in the process I came up with) maybe someone out there will take an improve upon it (which I sure can be done) but this will get you the information you want. There are a few settings you will need to make sure of and a couple tables you will need to make (or one you can import from the attached database). As far as the settings go - I made this in an Access 2000 (If you are working in another version let me know and I can move it and get you the version you are on), the references need to have a reference to the DAO3.6 and it needs to be at a higher priority in the reference list then Microsoft ActiveX Data objects 2.1 library (let me know if what I just wrote was in Greek to you). You will need to do a make table query that brings in all of the question fields from tblData and makes it into a table called TblDataQuesOnly. When you run the code you will need to run it from the debug window (I did not create a fancy form for you to run this form (as that is part of the "look pretty" portion of the project), so when you want to run this you will need to open the debug or immediate window (Ctrl-G on the keyboard) and then type in GetQuestionPercentage and press return. When the process is complete you will see the data in the new table "tblDataPct"

OK - having said all of that there is obviously a lot going on here so if you have any problems put in a reply and I will look at next week.

GumbyD

 

[GumbyD]

ccocom -

Were you able to get that to work?

GumbyD

 

[ccocom]

Thanks, Gumby. I have not been able to look at it again until today. The part about the active x is indeed greek to me. If you can dumb it down a little more, I would appreciate it. Take care.

 

[GumbyD]

Access references some outside code sources where it stores much of the functionality that you can use in the program. One of files it can reference is the DAO. Which I am using in the code I have written for you. To set or check to see if the reference is in place: from the database window press ctrl-G (that will bring up the immediate or debug window) then go to tools on the menu bar and select References. The selected references will be at the top of the list and will be checked. If you do not see a reference to the DAO 3.6 then press the browse button and go to C:\program files\common files\microsoft shared\DAO and look for the DAO360.dll and select it. Press OK on the references window and you are done.

Let me know if you need anymore assistance.

GumbyD