thanks for an elegant response...
"Don Leverton" <leveriteNoJunkMail@telusplanet.net> wrote in message
news:36Agc.49382$aD.12596@edtnps89...[color=blue]
> 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[/color]
calculations[color=blue]
>
> 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 cmdRandomResponse_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.OpenRecordset("tblQuestions", dbOpenTable)
> Set rstR = MyDB.OpenRecordset("tblResponses", 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((upperbound - 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 cmdTabulateResults_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.OpenRecordset("tblQuestions", dbOpenTable)
> Set rstResults = MyDB.OpenRecordset("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[/color]
time.[color=blue]
> MyQ = !QNbr
> With rstResults
> 'There should be 4 possible responses to each question, but[/color]
who[color=blue]
> knows for sure? This checks.
> MyMin = DMin("Response", "tblResponses", "([QNbr] = " & MyQ &
> ")")
> MyMax = DMax("Response", "tblResponses", "([QNbr] = " & MyQ &
> ")")
>
> For i = MyMin To MyMax
> .AddNew
> !QuestionNumber = MyQ
> !Response = i
> MyCountQ = DCount("QNbr", "tblResponses", "([QNbr] = " &[/color]
MyQ[color=blue]
> & ")")
> MyCountR = DCount("Response", "tblResponses", "([QNbr] = "[/color]
&[color=blue]
> MyQ & ") And ([Response] = " & i & ")")
>
> MyPcnt = (MyCountR / MyCountQ)
> !ResponsePercent = 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[/color]
immediately[color=blue]
> display the results
>
> End Sub
> ************************************************** **********
> --
> HTH,
> Don
> =============================
> Use
My.Name@Telus.Net 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" <tbrooks7_nospam_@mindspring.com> wrote in message
> news:u2ngc.12058$l75.2400@newsread2.news.atl.earth link.net...[color=green]
> > Hello,
> > I think the question i have is fairly straightforward, but I can't seem[/color][/color]
to[color=blue][color=green]
> > replicate the old SAS frequency procedure when I try to accomplish this[/color][/color]
in[color=blue][color=green]
> > MS Access.
> >
> > anyway, i have about 10 questions on a survey that have a possible[/color]
> response[color=green]
> > 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[/color][/color]
all[color=blue][color=green]
> > 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[/color]
> query[color=green]
> > and selecting the field twice, then using groupby in the first column[/color][/color]
and[color=blue][color=green]
> > count in the second column. But if I try to do this for the next field[/color][/color]
in[color=blue][color=green]
> > the next 2 columns it screws everything up.
> >
> > can someone help me?
> > thanks,
> > Tim Brooks
> >
> >[/color]
>
>[/color]
