Need help with averaging all values which are NOT null

[Guest]

I'm a newbie, so please be gentle with me...



I don't really know how to do calculations in a query. I've set up a report
based on a query, and with lots of trial & error managed to insert calculated
fields in the report footer which give me the total number of patients
treated, the percentage of these patients who have relapsed (i.e. their
disease recurred), and the average number of days between the end of
treatment and relapse. The problem is, I've got an average that includes null
values - and these should be excluded.

What is the best way to get this information into my report? Should I do
this in the query on which the report is based, or should I do this within
the report using a calculated control? And if the query is the best
approach, can you please walk me through the steps?

Appreciate all your help!

 

[Jeff Boyce]

How are you calculating the average? How do you know it includes records
with nulls?

In query design mode, you can:
* use the Totals toolbar button (greek sigma) to calculate an average (IF
your data structure is well-normalized)
* use the criterion of "Not Is Null" to exclude records w/ nulls

Bottom line is based on your starting point. Describe your data structure,
give an example...

 

[Guest]

You asked for more info - you may be sorry, as this is rather long-winded!

I have a total of 5 dates:
Chemo6 (for people who receive 6 months of chemotherapy... some need no more)
Chemo8 (for people who receive 8 months of chemotherapy)
Vaccine (for people who complete chemotherapy & then receive vaccine... but
some folks don't respond to chemotherapy & therefore don't receive vaccine)
Relapse (for people whose disease comes back at some point... This time
point varies from person to person. Not all people relapse, and if they do,
it may be after 6 months of chemotherapy, 8 months of chemotherapy, or
vaccine.

In my query, I figured out the last day of chemo as follows: LastChemo:
IIf([Chemo8]>[Chemo6],[Chemo8],[Chemo6])
In my query, I figured out the last day of therapy as follows: LastTx:
IIf([Vaccine]>[LastChemo],[Vaccine],[LastChemo])
Then in my query, I figured out the number of days between the end of
therapy (whether 6 or 8 months of chemo or vaccine) and relapse as follows:
DaysToRelapse: ([RelapseDate])-([LastTx])

In my report, I totalled the # of patients treated: =Count(*)
I then figured out the percentage of patients who were treated and, at some
point, relapsed: =Sum((IsNull([RelapseDate])+1))/Count(*)
I then figured out the average # of days til relapse: =Avg([DaysToRelapse])

I want to be certain that my "Percentage of patients in relapse" and
"Average # of days to relapse" are accurate. Some patients are still
undergoing treatment, so they haven't completed chemotherapy or received
vaccine. I want to exclude them when calculating the percentage of patients
in relapse. And some patients who have completed treatment haven't relapsed,
so they need to be excluded from "average # of days to relapse".

The point is, after all the calculations, I've gotten a bit "lost in space".
Do I need to fine-tune my calculations in either the report or the underlying
query to get what I want?

Many, many, many thanks!

 

[Jeff Boyce]

I'm not quite clear on how your data is organized ... and how you query it
depends on how you've stored it.

It seems possible that you have multiple columns recording different kinds
of dates in your table. Is this the case?

What might help would be a brief table definition (description), and an
example. I'll hold off on advancing to reporting until after I have a
better handle on the underlying data. Here's an example of a table
definition re: Persons...

tblPerson
PersonID
FirstName
LastName
DOB

(this is terribly simplified... please post your table definition(s))

--
Regards

Jeff Boyce
<Office/Access MVP>

You asked for more info - you may be sorry, as this is rather long-winded!

I have a total of 5 dates:
Chemo6 (for people who receive 6 months of chemotherapy... some need no more)
Chemo8 (for people who receive 8 months of chemotherapy)
Vaccine (for people who complete chemotherapy & then receive vaccine... but
some folks don't respond to chemotherapy & therefore don't receive vaccine)
Relapse (for people whose disease comes back at some point... This time
point varies from person to person. Not all people relapse, and if they do,
it may be after 6 months of chemotherapy, 8 months of chemotherapy, or
vaccine.

In my query, I figured out the last day of chemo as follows: LastChemo:
IIf([Chemo8]>[Chemo6],[Chemo8],[Chemo6])
In my query, I figured out the last day of therapy as follows: LastTx:
IIf([Vaccine]>[LastChemo],[Vaccine],[LastChemo])
Then in my query, I figured out the number of days between the end of
therapy (whether 6 or 8 months of chemo or vaccine) and relapse as follows:
DaysToRelapse: ([RelapseDate])-([LastTx])

In my report, I totalled the # of patients treated: =Count(*)
I then figured out the percentage of patients who were treated and, at some
point, relapsed: =Sum((IsNull([RelapseDate])+1))/Count(*)
I then figured out the average # of days til relapse: =Avg([DaysToRelapse])

I want to be certain that my "Percentage of patients in relapse" and
"Average # of days to relapse" are accurate. Some patients are still
undergoing treatment, so they haven't completed chemotherapy or received
vaccine. I want to exclude them when calculating the percentage of patients
in relapse. And some patients who have completed treatment haven't relapsed,
so they need to be excluded from "average # of days to relapse".

The point is, after all the calculations, I've gotten a bit "lost in space".
Do I need to fine-tune my calculations in either the report or the underlying
query to get what I want?

Many, many, many thanks!



--
Thanks for your time!

How are you calculating the average? How do you know it includes records
with nulls?

In query design mode, you can:
* use the Totals toolbar button (greek sigma) to calculate an average (IF
your data structure is well-normalized)
* use the criterion of "Not Is Null" to exclude records w/ nulls

Bottom line is based on your starting point. Describe your data structure,
give an example...


--
More info, please ...

Jeff Boyce
<Office/Access MVP>

includes
null

 

[Guest]

Basic table structure is like this (with *** fields being used in report
structure)

tblPerson
StudyID ***
LastName ***
FirstName ***

tblChemo
Month1Chemo ***
Month2Chemo
Month3Chemo
Month4Chemo
Month5Chemo
Month6Chemo ***
Month7Chemo
Month8Chemo ***

tblVaccine
Month1Vaccine
Month2Vaccine
Month3Vaccine
Month4Vaccine
Month6Vaccine ***

tblRelapse
Relapse (A Yes/No field) ***
RelapseDate ***

I have a calculated field within the report that determines the length of
time from last treatment (whether Month6Chemo, Month8Chemo, or Month6Vaccine)
until RelapseDate (if applicable).

Is this what you needed?

Appreciate your patience & assistance!

--
Thanks for your time!

I'm not quite clear on how your data is organized ... and how you query it
depends on how you've stored it.

It seems possible that you have multiple columns recording different kinds
of dates in your table. Is this the case?

What might help would be a brief table definition (description), and an
example. I'll hold off on advancing to reporting until after I have a
better handle on the underlying data. Here's an example of a table
definition re: Persons...

tblPerson
PersonID
FirstName
LastName
DOB

(this is terribly simplified... please post your table definition(s))

--
Regards

Jeff Boyce
<Office/Access MVP>

You asked for more info - you may be sorry, as this is rather long-winded!

I have a total of 5 dates:
Chemo6 (for people who receive 6 months of chemotherapy... some need no more)
Chemo8 (for people who receive 8 months of chemotherapy)
Vaccine (for people who complete chemotherapy & then receive vaccine... but
some folks don't respond to chemotherapy & therefore don't receive vaccine)
Relapse (for people whose disease comes back at some point... This time
point varies from person to person. Not all people relapse, and if they do,
it may be after 6 months of chemotherapy, 8 months of chemotherapy, or
vaccine.

In my query, I figured out the last day of chemo as follows: LastChemo:
IIf([Chemo8]>[Chemo6],[Chemo8],[Chemo6])
In my query, I figured out the last day of therapy as follows: LastTx:
IIf([Vaccine]>[LastChemo],[Vaccine],[LastChemo])
Then in my query, I figured out the number of days between the end of
therapy (whether 6 or 8 months of chemo or vaccine) and relapse as follows:
DaysToRelapse: ([RelapseDate])-([LastTx])

In my report, I totalled the # of patients treated: =Count(*)
I then figured out the percentage of patients who were treated and, at some
point, relapsed: =Sum((IsNull([RelapseDate])+1))/Count(*)
I then figured out the average # of days til relapse: =Avg([DaysToRelapse])

I want to be certain that my "Percentage of patients in relapse" and
"Average # of days to relapse" are accurate. Some patients are still
undergoing treatment, so they haven't completed chemotherapy or received
vaccine. I want to exclude them when calculating the percentage of patients
in relapse. And some patients who have completed treatment haven't relapsed,
so they need to be excluded from "average # of days to relapse".

The point is, after all the calculations, I've gotten a bit "lost in space".
Do I need to fine-tune my calculations in either the report or the underlying
query to get what I want?

Many, many, many thanks!



--
Thanks for your time!

How are you calculating the average? How do you know it includes records
with nulls?

In query design mode, you can:
* use the Totals toolbar button (greek sigma) to calculate an average (IF
your data structure is well-normalized)
* use the criterion of "Not Is Null" to exclude records w/ nulls

Bottom line is based on your starting point. Describe your data structure,
give an example...


--
More info, please ...

Jeff Boyce
<Office/Access MVP>

I'm a newbie, so please be gentle with me...



I don't really know how to do calculations in a query. I've set up a
report
based on a query, and with lots of trial & error managed to insert
calculated
fields in the report footer which give me the total number of patients
treated, the percentage of these patients who have relapsed (i.e. their
disease recurred), and the average number of days between the end of
treatment and relapse. The problem is, I've got an average that includes
null
values - and these should be excluded.

What is the best way to get this information into my report? Should I do
this in the query on which the report is based, or should I do this within
the report using a calculated control? And if the query is the best
approach, can you please walk me through the steps?

Appreciate all your help!

 

[Jeff Boyce]

From your earlier descriptions, I suspected you had "repeating fields" (e.g.
Month1Chemo, Month2Chemo...). While this design is necessary in a
spreadsheet, using it in Access (a relational database) makes it quite
difficult to use the features/functions.

I didn't notice a connection between persons and chemo and vaccines and
relapses. The tables you've described have no fields in common, a necessity
if you are to join them together in queries.

The table you offered first contains (apparently) person-related
information, but contains a "StudyID". Either each person is a "study" all
to him/herself, and no person can ever repeat treatment, or you'll have to
"duplicate" persons (John Doe participates twice, but has two separate
records). If the latter, how will you track John between his first and
second study?

I'm still not clear on how your tables are related, so reporting on them is
problematic.


--
Regards

Jeff Boyce
<Office/Access MVP>

Basic table structure is like this (with *** fields being used in report
structure)

tblPerson
StudyID ***
LastName ***
FirstName ***

tblChemo
Month1Chemo ***
Month2Chemo
Month3Chemo
Month4Chemo
Month5Chemo
Month6Chemo ***
Month7Chemo
Month8Chemo ***

tblVaccine
Month1Vaccine
Month2Vaccine
Month3Vaccine
Month4Vaccine
Month6Vaccine ***

tblRelapse
Relapse (A Yes/No field) ***
RelapseDate ***

I have a calculated field within the report that determines the length of
time from last treatment (whether Month6Chemo, Month8Chemo, or Month6Vaccine)
until RelapseDate (if applicable).

Is this what you needed?

Appreciate your patience & assistance!

--
Thanks for your time!

I'm not quite clear on how your data is organized ... and how you query it
depends on how you've stored it.

It seems possible that you have multiple columns recording different kinds
of dates in your table. Is this the case?

What might help would be a brief table definition (description), and an
example. I'll hold off on advancing to reporting until after I have a
better handle on the underlying data. Here's an example of a table
definition re: Persons...

tblPerson
PersonID
FirstName
LastName
DOB

(this is terribly simplified... please post your table definition(s))

--
Regards

Jeff Boyce
<Office/Access MVP>

You asked for more info - you may be sorry, as this is rather long-winded!

I have a total of 5 dates:
Chemo6 (for people who receive 6 months of chemotherapy... some need

no
more)

Chemo8 (for people who receive 8 months of chemotherapy)
Vaccine (for people who complete chemotherapy & then receive

vaccine...
but

some folks don't respond to chemotherapy & therefore don't receive vaccine)
Relapse (for people whose disease comes back at some point... This time
point varies from person to person. Not all people relapse, and if

they
do,

it may be after 6 months of chemotherapy, 8 months of chemotherapy, or
vaccine.

In my query, I figured out the last day of chemo as follows: LastChemo:
IIf([Chemo8]>[Chemo6],[Chemo8],[Chemo6])
In my query, I figured out the last day of therapy as follows: LastTx:
IIf([Vaccine]>[LastChemo],[Vaccine],[LastChemo])
Then in my query, I figured out the number of days between the end of
therapy (whether 6 or 8 months of chemo or vaccine) and relapse as follows:
DaysToRelapse: ([RelapseDate])-([LastTx])

In my report, I totalled the # of patients treated: =Count(*)
I then figured out the percentage of patients who were treated and, at some
point, relapsed: =Sum((IsNull([RelapseDate])+1))/Count(*)
I then figured out the average # of days til relapse: =Avg([DaysToRelapse])

I want to be certain that my "Percentage of patients in relapse" and
"Average # of days to relapse" are accurate. Some patients are still
undergoing treatment, so they haven't completed chemotherapy or received
vaccine. I want to exclude them when calculating the percentage of patients
in relapse. And some patients who have completed treatment haven't relapsed,
so they need to be excluded from "average # of days to relapse".

The point is, after all the calculations, I've gotten a bit "lost in space".
Do I need to fine-tune my calculations in either the report or the underlying
query to get what I want?

Many, many, many thanks!



--
Thanks for your time!


:

How are you calculating the average? How do you know it includes records
with nulls?

In query design mode, you can:
* use the Totals toolbar button (greek sigma) to calculate an

average
(IF

your data structure is well-normalized)
* use the criterion of "Not Is Null" to exclude records w/ nulls

Bottom line is based on your starting point. Describe your data structure,
give an example...


--
More info, please ...

Jeff Boyce
<Office/Access MVP>

I'm a newbie, so please be gentle with me...



I don't really know how to do calculations in a query. I've set up a
report
based on a query, and with lots of trial & error managed to insert
calculated
fields in the report footer which give me the total number of patients
treated, the percentage of these patients who have relapsed (i.e. their
disease recurred), and the average number of days between the end of
treatment and relapse. The problem is, I've got an average that includes
null
values - and these should be excluded.

What is the best way to get this information into my report?

Should I
do

this in the query on which the report is based, or should I do

this
within

the report using a calculated control? And if the query is the best
approach, can you please walk me through the steps?

Appreciate all your help!