percentage of total froma query

[Andre C]

I have a query linked to a form wher the person can select criteria
and the result is repesented as a report. All works fine.

The problem is how do I add a single line to the report which
calculates the percentage the query represents of the whole? I have a
suspicion the solution lies in DSUM but maybe I am wrong.

To clarify:

The database records all nurse contacts with patients. each patient
has their allocated nurse, doctor and disease.
The query at its basic level collects data from several tables. the
aim being to generate a list of all visits. The form then asks the
user to filter based around doctor or nurse or even month and year and
disease.

SO, foir instance the user can ask the form "show me all visits by
nurse Sarah of patients with disease computeritis for 2005." In VBA I
generate the filter thus

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [visitID] " & strvisitID & _
" AND [DiseaseID] " & strdisease & _
" And [visitmonth] " & strvisitmonth & _
" And [visityear] " & strvisityear & _
" And [Active] " & strcaseload

If SysCmd(acSysCmdGetObjectState, acReport, strReport) <>
acObjStateOpen Then
DoCmd.OpenReport strReport, acViewPreview
End If

' Apply the filter and switch it on
With Reports(strReport)
.Filter = strFilter
.FilterOn = True
End With


This works fine. It does what it says on the box.

But what I want is to be able to say at the bottom of the report is
the percentage of visits by nurse Sarah of patients with disease
computeritis for 2005 of all visits for nurse Sarah of patients for
2005. Of course the query no longer has that data as it has been
filtered in the VBA.

I thiught of creating another query, identical and filtering it minus
the disease-code. But I could not udnerstand how to sum the result and
add it to the report. I thought about subreports but again could not
get my mind around it.

The trouble is that both parts of the percentage are variables
dependin gon what the user sleelcts. i.e the user and potentially
select from the query form the following:

visits of nurse Sarah and nurse Jane of patients with disease
computeritis and nerditis for March 2005. It is very dynamic and so I
guessx the percentage figure will be dynamic.

Any ideas.

Andre

 

[Marshall Barton]

I have a query linked to a form wher the person can select criteria
and the result is repesented as a report. All works fine.

The problem is how do I add a single line to the report which
calculates the percentage the query represents of the whole? I have a
suspicion the solution lies in DSUM but maybe I am wrong.

To clarify:

The database records all nurse contacts with patients. each patient
has their allocated nurse, doctor and disease.
The query at its basic level collects data from several tables. the
aim being to generate a list of all visits. The form then asks the
user to filter based around doctor or nurse or even month and year and
disease.

SO, foir instance the user can ask the form "show me all visits by
nurse Sarah of patients with disease computeritis for 2005." In VBA I
generate the filter thus

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [visitID] " & strvisitID & _
" AND [DiseaseID] " & strdisease & _
" And [visitmonth] " & strvisitmonth & _
" And [visityear] " & strvisityear & _
" And [Active] " & strcaseload

If SysCmd(acSysCmdGetObjectState, acReport, strReport) <>
acObjStateOpen Then
DoCmd.OpenReport strReport, acViewPreview
End If

' Apply the filter and switch it on
With Reports(strReport)
.Filter = strFilter
.FilterOn = True
End With


This works fine. It does what it says on the box.

But what I want is to be able to say at the bottom of the report is
the percentage of visits by nurse Sarah of patients with disease
computeritis for 2005 of all visits for nurse Sarah of patients for
2005. Of course the query no longer has that data as it has been
filtered in the VBA.

I thiught of creating another query, identical and filtering it minus
the disease-code. But I could not udnerstand how to sum the result and
add it to the report. I thought about subreports but again could not
get my mind around it.

The trouble is that both parts of the percentage are variables
dependin gon what the user sleelcts. i.e the user and potentially
select from the query form the following:

visits of nurse Sarah and nurse Jane of patients with disease
computeritis and nerditis for March 2005. It is very dynamic and so I
guessx the percentage figure will be dynamic.

If the criteria without the disease does what you want, you
can use a DCount (or DSum??) like:

DCount("*", "yourtable", strFilterwithoutdisease)

 

[Andre C]

If the criteria without the disease does what you want, you
can use a DCount (or DSum??) like:

DCount("*", "yourtable", strFilterwithoutdisease)

I had a suspicion the solution lay in Dsum etc.

for "yourtable" can this be the query? Can I use the same query even
though it has already been filtered in the VBA script or should I
build an identical query minus disease?

can "strFilterwithoutdisease" be built in code. At present the VBA
code which is attatched to the preview report button on the form
builts the filter for query 1 and then opens the report with a call to
filter. So can I add some code to build "strFilterwithoutdisease" in
this?

Andre

 

[Marshall Barton]

I had a suspicion the solution lay in Dsum etc.

for "yourtable" can this be the query? Can I use the same query even
though it has already been filtered in the VBA script or should I
build an identical query minus disease?

can "strFilterwithoutdisease" be built in code. At present the VBA
code which is attatched to the preview report button on the form
builts the filter for query 1 and then opens the report with a call to
filter. So can I add some code to build "strFilterwithoutdisease" in
this?

yourtable should be replace with the name of whatever
table/query you are using as the report's record source.

The code to construct strFilterwithoutdisease would be
identical ti your existing filter code except it would not
include the disease criteria.

I do not understand why you want to sum the visits. Seems
to me you are just counting the visits or is there something
more going on?

 

[Andre C]

yourtable should be replace with the name of whatever
table/query you are using as the report's record source.

The code to construct strFilterwithoutdisease would be
identical ti your existing filter code except it would not
include the disease criteria.

I do not understand why you want to sum the visits. Seems
to me you are just counting the visits or is there something
more going on?

Yes each record has the duration in minutes and I want a grand total.
Ultimately I want to know what perfentage of time was spend on what
disease. That's easy except when the user only wants the report to
show a few diseases but still needs the overal time spent on the
entire caseload to create the percentages.

I will try your sugestion over the weekend and report back if probls
persist.

Andre C

 

[Andre C]

Thanks.

In VBA I have set up the Dsum and it seems to work, I store the result
in a variable of type LONG.

I created a text box in the report footer called Text23. Now how do I
get the result of the Dsum to the text box or is there a better way.

I thought After opening the report me!text23 would work but no.

 

[Marshall Barton]

Thanks.

In VBA I have set up the Dsum and it seems to work, I store the result
in a variable of type LONG.

I created a text box in the report footer called Text23. Now how do I
get the result of the Dsum to the text box or is there a better way.

I thought After opening the report me!text23 would work but no.

Use the Format event of the section containing the text box
(Report Footer):

Me.Text23 = variable

But, I don't see the need for the variable or code in this
case. Is there a reason why the text box can not just use
the DSum in its control source expression?

 

[Andre C]

But, I don't see the need for the variable or code in this
case. Is there a reason why the text box can not just use
the DSum in its control source expression?

Can you expand on this.

Currently the filter code is thus

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [visitID] " & strvisitID & _
" AND [DiseaseID] " & strdisease & _
" And [visitmonth] " & strvisitmonth & _
" And [visityear] " & strvisityear & _
" And [Active] " & strcaseload

This builds the basic report
Currently a second filter is built minus the disease part in code.

Are you saying that the above can be sirectly palced into the text box
data property as part of a (long) DSUM formula. Will it still
recognmise the variables which are all set in code?

By the way the above code is based on the stuff at
http://www.fontstuff.com/access/acctut19.htm

 

[Marshall Barton]

Scratch that idea, it can be done, but, after reviewing this
thread, I think it would be more trouble than it's worth.

I thought I said this before, but I'd better reiterate. You
can not stuff values into a report from a form. I find it
interesting that you can even change the Filter property of
a report that has already been opened. I never heard of
doing this, but if it works, it works.

To get what you want, I think you need to use a text box on
the form instead of a variable. The code in the form would
be like:

strFilter = . . .
Me.txtTotal = DSum("durationfield", "thetable", strFilter)

Then the Text23 text box in the report can display it by
using an expression:
=Forms!nameofform/txtTotal
--
Marsh
MVP [MS Access]

But, I don't see the need for the variable or code in this
case. Is there a reason why the text box can not just use
the DSum in its control source expression?

Can you expand on this.

Currently the filter code is thus

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [visitID] " & strvisitID & _
" AND [DiseaseID] " & strdisease & _
" And [visitmonth] " & strvisitmonth & _
" And [visityear] " & strvisityear & _
" And [Active] " & strcaseload

This builds the basic report
Currently a second filter is built minus the disease part in code.

Are you saying that the above can be sirectly palced into the text box
data property as part of a (long) DSUM formula. Will it still
recognmise the variables which are all set in code?

By the way the above code is based on the stuff at
http://www.fontstuff.com/access/acctut19.htm

 

[google]

Thanks that worked. Though it does seem a clumsy way. Why can't
variables be assigned to report text boxes. perhaps the next version?

Andre

 

[Marshall Barton]

Thanks that worked. Though it does seem a clumsy way. Why can't
variables be assigned to report text boxes. perhaps the next version?

Probably not in any version. The VBA environment is really
a separate component of the Access System with its own name
space. The only VBA items that are available for use pretty
much anywhere in the Access or SQL environments are Public
Functions in standard modules. This does provide a simple
way to retrieve some variables by using a function that does
nothing beyond returming the variable's value. E.g.

Public myvar As Long
Public Function GetMyVar() As Long
GetMyVar = myvar
End Function

But, in your case, you would have to save the value to the
global variable so the report text box can call the function
in its expression using =GetMyVar() which seems even more
round about than using the text box. Using a function like
this does have a significant benefit in that it makes the
report independent of the form.