Frequency Distribution question

[NC Tim]

Hello,
I think the question i have is fairly straightforward , but I can't seem to
replicate the old SAS frequency procedure when I try to accomplish this in
MS Access.

anyway, i have about 10 questions on a survey that have a possible response
range from 0-4.

what I would like to do is simply show that for each question we had x
amount of responses in each category, which amount to x percentage of all
responses:

so if we have the following responses to 5 surveys for question 1
1
2
2
3
4

the frequency distribution would look like:
1 1 20%
2 2 40%
3 1 20%
4 1 20%

I can get this (or at least the frequency) for one question by using a query
and selecting the field twice, then using groupby in the first column and
count in the second column. But if I try to do this for the next field in
the next 2 columns it screws everything up.

can someone help me?
thanks,
Tim Brooks

[Don Leverton]

Hi Tim,

I took this on as a challenge ... I like to do that occasionally as an
opportunity to learn. :)

Here's what I came up with, although I'm sure that someone will have a
better idea.
It's generally not a good idea to store calculated values, so I think the
correct method to accomplish this task might be to use a crosstab query
(which I suck at.)

My plan calls for 3 tables.
tblQuestions holds your 10 questions.
tblResponses holds the 100 responses to each of those questions. (one to
many join)
tblResults stores the counts the responses, and the percentage calculations

I did enter 10 questions into tblQuestions, but was too lazy to enter 100
random responses, so I made up some code to do that for me.
After I had the response data, I wrote some more code to write the
calculated data to tblResults.

My code runs from two command buttons on an unbound form, and the results
are displayed in a datasheet-style subform called "sbfResults ", which is
based on tblResults.

Hopefully the above will help you to understand how this code works:
*************** *************** *************** ***************
Option Compare Database
Option Explicit

Private Sub cmdRandomRespon se_Click()
'We have 10 questions, and want 100 random responses to each question

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rstQ As DAO.Recordset
Dim rstR As DAO.Recordset

Set rstQ = MyDB.OpenRecord set("tblQuestio ns", dbOpenTable)
Set rstR = MyDB.OpenRecord set("tblRespons es", dbOpenDynaset)

Dim i As Integer
Dim MyQ As Long
Dim MyR As Long

With rstQ
.MoveLast
.MoveFirst
Do Until .EOF 'Here are the 10 Questions
MyQ = !QNbr 'Both tables have a QNbr field (long integer --- 1 to
many)
With rstR
For i = 0 To 99 'Here is where the 100 random responses are
created
.AddNew
!QNbr = MyQ
'Int((upperboun d - lowerbound + 1) * Rnd + lowerbound)
!Response = Int((4 - 0 + 1) * Rnd + 0)
.Update
Next i
End With
.MoveNext
Loop

.Close
End With

rstR.Close

Set rstR = Nothing
Set rstQ = Nothing
Set MyDB = Nothing
End Sub
*************** *************** *************** ***************
Private Sub cmdTabulateResu lts_Click()

'Now we have 10 questions, 100 responses to each question, and want to
tabulate the results
Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rstQ As DAO.Recordset
Dim rstResults As DAO.Recordset

Set rstQ = MyDB.OpenRecord set("tblQuestio ns", dbOpenTable)
Set rstResults = MyDB.OpenRecord set("tblResults ", dbOpenDynaset)

Dim i As Integer
Dim MyQ As Long
Dim MyR As Long

Dim MyCountQ As Long
Dim MyCountR As Long
Dim MyPcnt

Dim MyMin
Dim MyMax

'Clear out the results table. The code below re-populates it with current
results.
MyDB.Execute "DELETE tblResults.* FROM tblResults;", dbFailOnError

With rstQ
.MoveLast
.MoveFirst
Do Until .EOF 'Here are the Questions ... Loop thru them one at a time.
MyQ = !QNbr
With rstResults
'There should be 4 possible responses to each question, but who
knows for sure? This checks.
MyMin = DMin("Response" , "tblRespons es", "([QNbr] = " & MyQ &
")")
MyMax = DMax("Response" , "tblRespons es", "([QNbr] = " & MyQ &
")")

For i = MyMin To MyMax
.AddNew
!QuestionNumber = MyQ
!Response = i
MyCountQ = DCount("QNbr", "tblRespons es", "([QNbr] = " & MyQ
& ")")
MyCountR = DCount("Respons e", "tblRespons es", "([QNbr] = " &
MyQ & ") And ([Response] = " & i & ")")

MyPcnt = (MyCountR / MyCountQ)
!ResponsePercen t = MyPcnt
!ResponseCount = MyCountR
.Update
Next i
End With
.MoveNext
Loop

.Close
End With
Set rstResults = Nothing
Set rstQ = Nothing
Set MyDB = Nothing

Me.Refresh 'I have a subform based on tblResults, so that I can immediately
display the results

End Sub
*************** *************** *************** ***************
--
HTH,
Don
=============== ==============
Use My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==
"NC Tim" <tb************ **@mindspring.c om> wrote in message
news:u2******** **********@news read2.news.atl. earthlink.net.. .

Hello,
I think the question i have is fairly straightforward , but I can't seem to
replicate the old SAS frequency procedure when I try to accomplish this in
MS Access.

anyway, i have about 10 questions on a survey that have a possible response range from 0-4.

what I would like to do is simply show that for each question we had x
amount of responses in each category, which amount to x percentage of all
responses:

so if we have the following responses to 5 surveys for question 1
1
2
2
3
4

the frequency distribution would look like:
1 1 20%
2 2 40%
3 1 20%
4 1 20%

I can get this (or at least the frequency) for one question by using a query and selecting the field twice, then using groupby in the first column and
count in the second column. But if I try to do this for the next field in
the next 2 columns it screws everything up.

can someone help me?
thanks,
Tim Brooks

[Dave Brydon]

Don,

My wife is considering a small survey at work, and is looking for options;
if possible, I would certainly appreciate an example of your database to
pass along to her.

Cheers,

Dave (db*****@ns.sym patico.ca)
(Please Zip file, or change extension, i.e. mydatabase.txt)

"Don Leverton" <le************ ****@telusplane t.net> wrote in message
news:36Agc.4938 2$aD.12596@edtn ps89...

Hi Tim,

I took this on as a challenge ... I like to do that occasionally as an
opportunity to learn. :)

Here's what I came up with, .......>

[Dave Brydon]

Don,

My wife is considering a small survey at work, and is looking for options;
if possible, I would certainly appreciate an example of your database to
pass along to her.

Cheers,

Dave (db*****@ns.sym patico.ca)
(Please Zip file, or change extension, i.e. mydatabase.txt)

"Don Leverton" <le************ ****@telusplane t.net> wrote in message
news:36Agc.4938 2$aD.12596@edtn ps89...

Hi Tim,

I took this on as a challenge ... I like to do that occasionally as an
opportunity to learn. :)

Here's what I came up with, .......>

[NC Tim]

thanks for an elegant response...

"Don Leverton" <le************ ****@telusplane t.net> wrote in message
news:36Agc.4938 2$aD.12596@edtn ps89...

Hi Tim,

I took this on as a challenge ... I like to do that occasionally as an
opportunity to learn. :)

Here's what I came up with, although I'm sure that someone will have a
better idea.
It's generally not a good idea to store calculated values, so I think the
correct method to accomplish this task might be to use a crosstab query
(which I suck at.)

My plan calls for 3 tables.
tblQuestions holds your 10 questions.
tblResponses holds the 100 responses to each of those questions. (one to
many join)
tblResults stores the counts the responses, and the percentage calculations
I did enter 10 questions into tblQuestions, but was too lazy to enter 100
random responses, so I made up some code to do that for me.
After I had the response data, I wrote some more code to write the
calculated data to tblResults.

My code runs from two command buttons on an unbound form, and the results
are displayed in a datasheet-style subform called "sbfResults ", which is
based on tblResults.

Hopefully the above will help you to understand how this code works:
*************** *************** *************** ***************
Option Compare Database
Option Explicit

Private Sub cmdRandomRespon se_Click()
'We have 10 questions, and want 100 random responses to each question

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rstQ As DAO.Recordset
Dim rstR As DAO.Recordset

Set rstQ = MyDB.OpenRecord set("tblQuestio ns", dbOpenTable)
Set rstR = MyDB.OpenRecord set("tblRespons es", dbOpenDynaset)

Dim i As Integer
Dim MyQ As Long
Dim MyR As Long

With rstQ
.MoveLast
.MoveFirst
Do Until .EOF 'Here are the 10 Questions
MyQ = !QNbr 'Both tables have a QNbr field (long integer --- 1 to
many)
With rstR
For i = 0 To 99 'Here is where the 100 random responses are
created
.AddNew
!QNbr = MyQ
'Int((upperboun d - lowerbound + 1) * Rnd + lowerbound)
!Response = Int((4 - 0 + 1) * Rnd + 0)
.Update
Next i
End With
.MoveNext
Loop

.Close
End With

rstR.Close

Set rstR = Nothing
Set rstQ = Nothing
Set MyDB = Nothing
End Sub
*************** *************** *************** ***************
Private Sub cmdTabulateResu lts_Click()

'Now we have 10 questions, 100 responses to each question, and want to
tabulate the results
Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rstQ As DAO.Recordset
Dim rstResults As DAO.Recordset

Set rstQ = MyDB.OpenRecord set("tblQuestio ns", dbOpenTable)
Set rstResults = MyDB.OpenRecord set("tblResults ", dbOpenDynaset)

Dim i As Integer
Dim MyQ As Long
Dim MyR As Long

Dim MyCountQ As Long
Dim MyCountR As Long
Dim MyPcnt

Dim MyMin
Dim MyMax

'Clear out the results table. The code below re-populates it with current
results.
MyDB.Execute "DELETE tblResults.* FROM tblResults;", dbFailOnError

With rstQ
.MoveLast
.MoveFirst
Do Until .EOF 'Here are the Questions ... Loop thru them one at a time. MyQ = !QNbr
With rstResults
'There should be 4 possible responses to each question, but who knows for sure? This checks.
MyMin = DMin("Response" , "tblRespons es", "([QNbr] = " & MyQ &
")")
MyMax = DMax("Response" , "tblRespons es", "([QNbr] = " & MyQ &
")")

For i = MyMin To MyMax
.AddNew
!QuestionNumber = MyQ
!Response = i
MyCountQ = DCount("QNbr", "tblRespons es", "([QNbr] = " & MyQ & ")")
MyCountR = DCount("Respons e", "tblRespons es", "([QNbr] = " & MyQ & ") And ([Response] = " & i & ")")

MyPcnt = (MyCountR / MyCountQ)
!ResponsePercen t = MyPcnt
!ResponseCount = MyCountR
.Update
Next i
End With
.MoveNext
Loop

.Close
End With
Set rstResults = Nothing
Set rstQ = Nothing
Set MyDB = Nothing

Me.Refresh 'I have a subform based on tblResults, so that I can immediately display the results

End Sub
*************** *************** *************** ***************
--
HTH,
Don
=============== ==============
Use My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==
"NC Tim" <tb************ **@mindspring.c om> wrote in message
news:u2******** **********@news read2.news.atl. earthlink.net.. .

Hello,
I think the question i have is fairly straightforward , but I can't seem to replicate the old SAS frequency procedure when I try to accomplish this in MS Access.

anyway, i have about 10 questions on a survey that have a possible

response

range from 0-4.

what I would like to do is simply show that for each question we had x
amount of responses in each category, which amount to x percentage of all responses:

so if we have the following responses to 5 surveys for question 1
1
2
2
3
4

the frequency distribution would look like:
1 1 20%
2 2 40%
3 1 20%
4 1 20%

I can get this (or at least the frequency) for one question by using a

query

and selecting the field twice, then using groupby in the first column and count in the second column. But if I try to do this for the next field in the next 2 columns it screws everything up.

can someone help me?
thanks,
Tim Brooks

[NC Tim]

thanks for an elegant response...

"Don Leverton" <le************ ****@telusplane t.net> wrote in message
news:36Agc.4938 2$aD.12596@edtn ps89...

Hi Tim,

I took this on as a challenge ... I like to do that occasionally as an
opportunity to learn. :)

Here's what I came up with, although I'm sure that someone will have a
better idea.
It's generally not a good idea to store calculated values, so I think the
correct method to accomplish this task might be to use a crosstab query
(which I suck at.)

My plan calls for 3 tables.
tblQuestions holds your 10 questions.
tblResponses holds the 100 responses to each of those questions. (one to
many join)
tblResults stores the counts the responses, and the percentage calculations
I did enter 10 questions into tblQuestions, but was too lazy to enter 100
random responses, so I made up some code to do that for me.
After I had the response data, I wrote some more code to write the
calculated data to tblResults.

My code runs from two command buttons on an unbound form, and the results
are displayed in a datasheet-style subform called "sbfResults ", which is
based on tblResults.

Hopefully the above will help you to understand how this code works:
*************** *************** *************** ***************
Option Compare Database
Option Explicit

Private Sub cmdRandomRespon se_Click()
'We have 10 questions, and want 100 random responses to each question

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rstQ As DAO.Recordset
Dim rstR As DAO.Recordset

Set rstQ = MyDB.OpenRecord set("tblQuestio ns", dbOpenTable)
Set rstR = MyDB.OpenRecord set("tblRespons es", dbOpenDynaset)

Dim i As Integer
Dim MyQ As Long
Dim MyR As Long

With rstQ
.MoveLast
.MoveFirst
Do Until .EOF 'Here are the 10 Questions
MyQ = !QNbr 'Both tables have a QNbr field (long integer --- 1 to
many)
With rstR
For i = 0 To 99 'Here is where the 100 random responses are
created
.AddNew
!QNbr = MyQ
'Int((upperboun d - lowerbound + 1) * Rnd + lowerbound)
!Response = Int((4 - 0 + 1) * Rnd + 0)
.Update
Next i
End With
.MoveNext
Loop

.Close
End With

rstR.Close

Set rstR = Nothing
Set rstQ = Nothing
Set MyDB = Nothing
End Sub
*************** *************** *************** ***************
Private Sub cmdTabulateResu lts_Click()

'Now we have 10 questions, 100 responses to each question, and want to
tabulate the results
Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rstQ As DAO.Recordset
Dim rstResults As DAO.Recordset

Set rstQ = MyDB.OpenRecord set("tblQuestio ns", dbOpenTable)
Set rstResults = MyDB.OpenRecord set("tblResults ", dbOpenDynaset)

Dim i As Integer
Dim MyQ As Long
Dim MyR As Long

Dim MyCountQ As Long
Dim MyCountR As Long
Dim MyPcnt

Dim MyMin
Dim MyMax

'Clear out the results table. The code below re-populates it with current
results.
MyDB.Execute "DELETE tblResults.* FROM tblResults;", dbFailOnError

With rstQ
.MoveLast
.MoveFirst
Do Until .EOF 'Here are the Questions ... Loop thru them one at a time. MyQ = !QNbr
With rstResults
'There should be 4 possible responses to each question, but who knows for sure? This checks.
MyMin = DMin("Response" , "tblRespons es", "([QNbr] = " & MyQ &
")")
MyMax = DMax("Response" , "tblRespons es", "([QNbr] = " & MyQ &
")")

For i = MyMin To MyMax
.AddNew
!QuestionNumber = MyQ
!Response = i
MyCountQ = DCount("QNbr", "tblRespons es", "([QNbr] = " & MyQ & ")")
MyCountR = DCount("Respons e", "tblRespons es", "([QNbr] = " & MyQ & ") And ([Response] = " & i & ")")

MyPcnt = (MyCountR / MyCountQ)
!ResponsePercen t = MyPcnt
!ResponseCount = MyCountR
.Update
Next i
End With
.MoveNext
Loop

.Close
End With
Set rstResults = Nothing
Set rstQ = Nothing
Set MyDB = Nothing

Me.Refresh 'I have a subform based on tblResults, so that I can immediately display the results

End Sub
*************** *************** *************** ***************
--
HTH,
Don
=============== ==============
Use My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==
"NC Tim" <tb************ **@mindspring.c om> wrote in message
news:u2******** **********@news read2.news.atl. earthlink.net.. .

Hello,
I think the question i have is fairly straightforward , but I can't seem to replicate the old SAS frequency procedure when I try to accomplish this in MS Access.

anyway, i have about 10 questions on a survey that have a possible

response

range from 0-4.

what I would like to do is simply show that for each question we had x
amount of responses in each category, which amount to x percentage of all responses:

so if we have the following responses to 5 surveys for question 1
1
2
2
3
4

the frequency distribution would look like:
1 1 20%
2 2 40%
3 1 20%
4 1 20%

I can get this (or at least the frequency) for one question by using a

query

and selecting the field twice, then using groupby in the first column and count in the second column. But if I try to do this for the next field in the next 2 columns it screws everything up.

can someone help me?
thanks,
Tim Brooks

[Wayne]

I made a table, tblResponses, containing the fields to hold the data,
2 queries to calculate the frequencies and a third query to present
the data in the form: QNumber, Response, ResponseFrequen cy,
ResponsePercent .

tblResponses:
ResponsesRID (an autonumber field as primary key)
ResponsesQNumbe r (the question number)
ResponsesRespon seNumber (1-4 I guess)

qryResponseFreq uency:
(counts frequency of each response to each question)
SELECT tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number,
Count(tblRespon ses.ResponsesQN umber) AS ResponseFrequen cy
FROM tblResponses
GROUP BY tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number, tblResponses.Re sponsesQNumber;

qryTotalRespons eFrequency:
(counts total number of all responses to each question)
SELECT tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number,
Count(tblRespon ses.ResponsesQN umber) AS ResponseFrequen cy
FROM tblResponses
GROUP BY tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number;

qryPercentages:
(uses the previous 2 queries to give you the Percentages you wanted)
SELECT qryResponseFreq uency.Responses QNumber AS QNumber,
qryResponseFreq uency.Responses ResponseNumber AS Response,
qryResponseFreq uency.ResponseF requency,
qryResponseFreq uency.ResponseF requency/qryTotalRespons eFrequency.Tota lResponseFreque ncy*100
AS ResponsePercent
FROM qryTotalRespons eFrequency INNER JOIN qryResponseFreq uency ON
qryTotalRespons eFrequency.Resp onsesQNumber =
qryResponseFreq uency.Responses QNumber;

[Wayne]

I made a table, tblResponses, containing the fields to hold the data,
2 queries to calculate the frequencies and a third query to present
the data in the form: QNumber, Response, ResponseFrequen cy,
ResponsePercent .

tblResponses:
ResponsesRID (an autonumber field as primary key)
ResponsesQNumbe r (the question number)
ResponsesRespon seNumber (1-4 I guess)

qryResponseFreq uency:
(counts frequency of each response to each question)
SELECT tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number,
Count(tblRespon ses.ResponsesQN umber) AS ResponseFrequen cy
FROM tblResponses
GROUP BY tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number, tblResponses.Re sponsesQNumber;

qryTotalRespons eFrequency:
(counts total number of all responses to each question)
SELECT tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number,
Count(tblRespon ses.ResponsesQN umber) AS ResponseFrequen cy
FROM tblResponses
GROUP BY tblResponses.Re sponsesQNumber,
tblResponses.Re sponsesResponse Number;

qryPercentages:
(uses the previous 2 queries to give you the Percentages you wanted)
SELECT qryResponseFreq uency.Responses QNumber AS QNumber,
qryResponseFreq uency.Responses ResponseNumber AS Response,
qryResponseFreq uency.ResponseF requency,
qryResponseFreq uency.ResponseF requency/qryTotalRespons eFrequency.Tota lResponseFreque ncy*100
AS ResponsePercent
FROM qryTotalRespons eFrequency INNER JOIN qryResponseFreq uency ON
qryTotalRespons eFrequency.Resp onsesQNumber =
qryResponseFreq uency.Responses QNumber;

[SAM]

I have done some reports for a series of questions in a survey with
similar answer choices. Basically, you have to treat each question
and its set of answers separately and then join them back together
later.

The GROUP BY Field1, Count(Field1) is fine, but when you introduce
additional fields, then the GROUP BY must account for them. I sometimes
think of the GROUP BY as working similar to a SELECT DISTINCT.

SAM

"NC Tim" <tb************ **@mindspring.c om> wrote in message news:<u2******* ***********@new sread2.news.atl .earthlink.net> ...

Hello,
I think the question i have is fairly straightforward , but I can't seem to
replicate the old SAS frequency procedure when I try to accomplish this in
MS Access.

anyway, i have about 10 questions on a survey that have a possible response
range from 0-4.

what I would like to do is simply show that for each question we had x
amount of responses in each category, which amount to x percentage of all
responses:

so if we have the following responses to 5 surveys for question 1
1
2
2
3
4

the frequency distribution would look like:
1 1 20%
2 2 40%
3 1 20%
4 1 20%

I can get this (or at least the frequency) for one question by using a query
and selecting the field twice, then using groupby in the first column and
count in the second column. But if I try to do this for the next field in
the next 2 columns it screws everything up.

can someone help me?
thanks,
Tim Brooks

[SAM]

I have done some reports for a series of questions in a survey with
similar answer choices. Basically, you have to treat each question
and its set of answers separately and then join them back together
later.

The GROUP BY Field1, Count(Field1) is fine, but when you introduce
additional fields, then the GROUP BY must account for them. I sometimes
think of the GROUP BY as working similar to a SELECT DISTINCT.

SAM

"NC Tim" <tb************ **@mindspring.c om> wrote in message news:<u2******* ***********@new sread2.news.atl .earthlink.net> ...

Hello,
I think the question i have is fairly straightforward , but I can't seem to
replicate the old SAS frequency procedure when I try to accomplish this in
MS Access.

anyway, i have about 10 questions on a survey that have a possible response
range from 0-4.

what I would like to do is simply show that for each question we had x
amount of responses in each category, which amount to x percentage of all
responses:

so if we have the following responses to 5 surveys for question 1
1
2
2
3
4

the frequency distribution would look like:
1 1 20%
2 2 40%
3 1 20%
4 1 20%

I can get this (or at least the frequency) for one question by using a query
and selecting the field twice, then using groupby in the first column and
count in the second column. But if I try to do this for the next field in
the next 2 columns it screws everything up.

can someone help me?
thanks,
Tim Brooks