Dcount & Reports from Form

[dom]

Hello and thank you for your time.

BackGround - I work at a hospital and we have a survey
which contains 20 questions, this survey is received from
6 departments. I was asked to build a database with a
report (among many others) which does the following.

1) Allow users to select which department to display
2) Allow users to select report by quarter, date range, or
year.
3) Report should contain all 20 questions with a
percentage of Yes vs. Yes/No.
4) Report also contain over all percentiles of the
percents being given above. (hope that makes sense)
5) Calculations are to be displayed on a form as well as a
report.
6) User should be able to cancel if the operation is
taking too long.

My task;
NOTE: ( the counts are based from different queries, for
my example I’m using the procedure that calls for the Year
query “qsPSDB_m” which is filtered by the reports form.)
a) Count each question that has 1 as a value
b) Count each question that has value of 1 as well as 2
c) Divide 1st count by 2nd count

********************* Begin Code ************************
Private cmdUpdate_OnClick()
Dim i As Integer
Dim lCountOne As Long
Dim lCountOneAndTwo As Long

‘ Calculate January

For i = 1 To 20
Me.lblLine1.Caption = "Processing January" & ":
Calculating Question " & i
If DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q" & i & "]
IN (1,2)") >= 1 Then
lCountOne = DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q"
& i & "] =1")
lCountOneAndTwo = DCount("sysID", "qsPSDB_m", "[Month]=1
AND [q" & i & "] IN (1,2)")
Me("txtA" & i) = lCountOne / lCountOneAndTwo
Else
Me("txtA" & i) = 0
End If
Me.Repaint
Next i

‘ Calculate February

For i = 1 To 20
Me.lblLine1.Caption = "Processing January" & ":
Calculating Question " & i
If DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q" & i & "]
IN (1,2)") >= 1 Then
lCountOne = DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q"
& i & "] =1")
lCountOneAndTwo = DCount("sysID", "qsPSDB_m", "[Month]=1
AND [q" & i & "] IN (1,2)")
Me("txtA" & i) = lCountOne / lCountOneAndTwo
Else
Me("txtA" & i) = 0
End If
Me.Repaint
Next i

‘ and so on …

End Sub
********************* End Code ************************

I’m almost done with everything but a few things.
i. I don’t know how to cancel when it takes too long. (any
suggestion ?)
ii. I tried placing the code(modified) on a report
(OnOpen_Event) a report but it looked at me like I’m
stupid and told me (very rudely I might add) You can’t
assign a value to this object. (any suggestions ?)
iii. If possible transfer all the values to an excel
spreadsheet.

If you would like to see a screenshot please let me know.

 

[John Spencer (MVP)]

Hard to answer in detail without knowing your table structure, but have you
considered a totals query? I'm guessing that all your questions are in one
table that has a structure something like:

TableTest
DeptID
TestDate
PersID
Q1
Q2
Q3
....
Q20

This is NOT a good design, but if that is what you have you can work with it.
You would be able to get all the data in one query that would look something
like the following for a 2nd quarter 2003 annual report.

SELECT Format(TestDate,"YYYY-Q") as TestPeriod
Abs(SUM(Q1=1)) as Q1Yes,
Count(Q1) as Q1Responses,
Abs(SUM(Q1=1))/Count(Q1) as Q1PerCent,
Abs(SUM(Q2=1)) as Q2Yes,
Count(Q2) as Q1Responses,
Abs(SUM(Q2=1))/Count(Q2) as Q1PerCent,
....
Abs(SUM(Q20=1)) as Q20Yes,
Count(Q20) as Q1Responses,
Abs(SUM(Q20=2))/Count(Q20) as Q1PerCent
FROM TableTest
WHERE Format(TestDate,"YYYY-Q") = "2003-2" AND
DeptID = 2
GROUP BY Format(TestDate,"YYYY-Q")

You could make this a lot simpler for the users by giving them a form that would
allow them to specify the parameter values for the where clause and then either
building the sql statement on the fly and using it as the record source for a
form/report. Or alternatively, using the form's controls as parameters in the
where clause

Something like:
SELECT ...
WHERE DeptID = Forms!SelectReport!ComboboxDepartment
AND TestDate Between Forms!SelectReport!txtBoxStartDate and
Forms!SelectReport!txtBoxEndDate

Hello and thank you for your time.

BackGround - I work at a hospital and we have a survey
which contains 20 questions, this survey is received from
6 departments. I was asked to build a database with a
report (among many others) which does the following.

1) Allow users to select which department to display
2) Allow users to select report by quarter, date range, or
year.
3) Report should contain all 20 questions with a
percentage of Yes vs. Yes/No.
4) Report also contain over all percentiles of the
percents being given above. (hope that makes sense)
5) Calculations are to be displayed on a form as well as a
report.
6) User should be able to cancel if the operation is
taking too long.

My task;
NOTE: ( the counts are based from different queries, for
my example I’m using the procedure that calls for the Year
query “qsPSDB_m” which is filtered by the reports form.)
a) Count each question that has 1 as a value
b) Count each question that has value of 1 as well as 2
c) Divide 1st count by 2nd count

********************* Begin Code ************************
Private cmdUpdate_OnClick()
Dim i As Integer
Dim lCountOne As Long
Dim lCountOneAndTwo As Long

‘ Calculate January

For i = 1 To 20
Me.lblLine1.Caption = "Processing January" & ":
Calculating Question " & i
If DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q" & i & "]
IN (1,2)") >= 1 Then
lCountOne = DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q"
& i & "] =1")
lCountOneAndTwo = DCount("sysID", "qsPSDB_m", "[Month]=1
AND [q" & i & "] IN (1,2)")
Me("txtA" & i) = lCountOne / lCountOneAndTwo
Else
Me("txtA" & i) = 0
End If
Me.Repaint
Next i

‘ Calculate February

For i = 1 To 20
Me.lblLine1.Caption = "Processing January" & ":
Calculating Question " & i
If DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q" & i & "]
IN (1,2)") >= 1 Then
lCountOne = DCount("sysID", "qsPSDB_m", "[Month]=1 AND [q"
& i & "] =1")
lCountOneAndTwo = DCount("sysID", "qsPSDB_m", "[Month]=1
AND [q" & i & "] IN (1,2)")
Me("txtA" & i) = lCountOne / lCountOneAndTwo
Else
Me("txtA" & i) = 0
End If
Me.Repaint
Next i

‘ and so on …

End Sub
********************* End Code ************************

I’m almost done with everything but a few things.
i. I don’t know how to cancel when it takes too long. (any
suggestion ?)
ii. I tried placing the code(modified) on a report
(OnOpen_Event) a report but it looked at me like I’m
stupid and told me (very rudely I might add) You can’t
assign a value to this object. (any suggestions ?)
iii. If possible transfer all the values to an excel
spreadsheet.

If you would like to see a screenshot please let me know.