Access database reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work in a Hospital giving Anesthesia for various operations. After their
operation, we measure each patient's pain score (Visual analogue score- VAS)
for a few days, to check they have enough analgesia on-board. The data is
gathered by our Pain Nurses into an Access Database. I summarise the data
monthly, but there are problems. Access doesn't add up the pain scores
accurately and so gives a false average. For eg: Plastic patients for
September; 6 patients with scores 10, 5,5,4,0,1 while at rest on Day1.
Access adds this up to be 31 (and not 25) and gives the average pain score as
5.2 and not 4.2. It gets worse for Day1_Moving. There were only 2 values
recorded, 10 and 0. Yet Access says the sum is 12 and the average is 6.
What is happening, and how can I stop it. (NB there were many more
Orthopaedic, General, ObsGynae, etc, so I can't import the data into Excel)
 
Numbers in Access reports are nearly always what the designer asks for. I
expect your query is messed up but it is difficult to determine without
seeing sample records or your SQL.
 
Thankyou. The printout of the report and the query results are as follows:
EntryDate NHI VAS_AtRest_Day1 VAS_Moving_Day1 VAS_AtRest_Day2 VAS_Moving_Day2 VAS_AtRest_Day3 VAS_Moving_Day3 Service Year Month
14/09/2005 FVM8409 3 7 Orthopaedic 2005 9
01/09/2005 EHK9252 0 9 Orthopaedic 2005 9
15/09/2005 FTQ2513 5 Orthopaedic 2005 9
05/09/2005 ATW5002 0 6 Orthopaedic 2005 9
28/09/2005 SFB2704 4 Plastic 2005 9
26/09/2005 BPX7049 10 10 6 10 0 Plastic 2005 9
28/09/2005 FKR9398 5 Plastic 2005 9
25/09/2005 SEZ5547 5 0 6 Plastic 2005 9
03/09/2005 SEN2114 1 1 Plastic 2005 9
30/09/2005 FTX2015 0 0 Plastic 2005 9
VAS scores By Surgical 9 / 2005
Servic VAS VAS VAS

Day 1
General
(75 detail records)
Avg SumDay1_Resti 228 Resting 3.2 3.3 2.8
SumDay1_Movi 382 Moving 6.0 6.0 5.1
Gynaecology
(10 detail records)
Avg SumDay1_Resti 50 Resting 5.0
SumDay1_Movi 35 Moving 5.0
Obstetric
(71 detail records)
Avg SumDay1_Resti 188 Resting 2.7 1.2
SumDay1_Movi 340 Moving 5.6 4.4
Orthopaedic
(79 detail records)
Avg SumDay1_Resti 213 Resting 2.9 2.8 4.1
SumDay1_Movi 284 Moving 5.2 5.0 5.7
Plastic
(6 detail records)
Avg SumDay1_Resti 31 Resting 5.2 4.5 1.0
SumDay1_Movi 12 Moving 6.0 11.0 7.0
I hope this helps. If not, I can send the original files, if you tell me
where
 
I'm sorry but I can't make heads or tails of your data. It looks like you
have an un-normalized table but I can't confirm this. Also, it seems that
some rows have 7 values while others have 10.

My suggestion is to simplify your report/query by limiting it to just a few
records and checking for accuracy. Keep adding records or complexity until
it breaks. Remember what you did to break it and then find out why.
 
Dear Duane
The original table lists each patient's code number, the date, the surgical
service looking after her (Orthopaedics, plastics, gynaecology, etc.), and
the numeric pain scores (Formatted as number, long integer) for both resting
and moving for day1, day2, day3, etc. The numeric pain scores are obtained
by asking the patient to "score" their pain on a scale of 0 through 10, with
"0" being no pain at all, "5" being moderate pain, and "10" being unbearable
pain. The query to look at this has 2 extra expressions,
exp1:Year([OperationDate]), and exp2:Month([OperationDate]). In the criteria
row, I put [Input the year you want], and [Input the month you want]. The
Report is designed with the wizard, based on this query, and summary only, to
show the average pain scores for each surgical service.
Now, I have discovered what is going wrong, I think. To each pain score the
Report is adding 1. Thus the number "0" becomes "1", and the number "10"
becomes "11", etc. This is why, on Day1 while moving, the plastic patient
scores were not 10 and 0, as they are in the query, but 11 and 1 (There were
only 6 plastic patients during September, and scores were only obtained while
moving in 2 of these. Unfortunately there are quite a few missing values)
Thus the Report summary gives the average pain score while moving on Day1 as
6, because it thinks the total is 12. But, obviously, it should read an
average of 5. Similarly with the Day1 at rest Plastic scores. The numbers
add up to 25, but since Access adds 1 to each number, and there are 6 of
these, it makes the total as 31, and hence the average as 31/6, which is 5.2.
It should calculate 25/6, which is 4.2.
If one "Exports" the query to Excel, all the numbers have 1 added and the
numbers are the same as in the Access Report. However, if one clicks
"Tools/Analyse with Microsoft Office Excel", the data is exported without any
addition to the numbers. However, the warning box on each cell says the
"numbers" are, in fact, "text". One can change this by selecting an empty
cell, copying that cell, selecting all the numbers which are "text", pasting
Special, and under objective, clicking multiply. This converts all the text
values to number values.

Do you know why this is happening and how I can stop it?
 
Access Reports do not arbitrarily add 1 to the numbers provided in their
Record Source. They just do not. Nor do they write anything back to the
Record Source.

The fact that the values all have 1 added when you Export that same Query to
Excel would indicate that the problem is either in the Query or in the data
in the underlying Table itself. I'd say the experimentation you did with
"Tools/Analyse..." would indicate it is probably the Query.

First, open the Table in Table View, and confirm for yourself (and us) that
the numbers are correct, as entered.

Next, open you Report in Design View, click in the upperleftmost little
square, choose Properties, under Data, select RecordSource. If what you see
there is SQL, copy it and post it here. If what you see is the name of a
Query, open that Query in Design View, select SQL View, copy the SQL and
post it here.

Perhaps with that information, someone can assist, or at least suggest
additional information that would help in debugging the problem.

Larry Linson
Microsoft Access MVP


JimTC said:
Dear Duane
The original table lists each patient's code number, the date, the
surgical
service looking after her (Orthopaedics, plastics, gynaecology, etc.), and
the numeric pain scores (Formatted as number, long integer) for both
resting
and moving for day1, day2, day3, etc. The numeric pain scores are
obtained
by asking the patient to "score" their pain on a scale of 0 through 10,
with
"0" being no pain at all, "5" being moderate pain, and "10" being
unbearable
pain. The query to look at this has 2 extra expressions,
exp1:Year([OperationDate]), and exp2:Month([OperationDate]). In the
criteria
row, I put [Input the year you want], and [Input the month you want]. The
Report is designed with the wizard, based on this query, and summary only,
to
show the average pain scores for each surgical service.
Now, I have discovered what is going wrong, I think. To each pain score
the
Report is adding 1. Thus the number "0" becomes "1", and the number "10"
becomes "11", etc. This is why, on Day1 while moving, the plastic patient
scores were not 10 and 0, as they are in the query, but 11 and 1 (There
were
only 6 plastic patients during September, and scores were only obtained
while
moving in 2 of these. Unfortunately there are quite a few missing values)
Thus the Report summary gives the average pain score while moving on Day1
as
6, because it thinks the total is 12. But, obviously, it should read an
average of 5. Similarly with the Day1 at rest Plastic scores. The
numbers
add up to 25, but since Access adds 1 to each number, and there are 6 of
these, it makes the total as 31, and hence the average as 31/6, which is
5.2.
It should calculate 25/6, which is 4.2.
If one "Exports" the query to Excel, all the numbers have 1 added and the
numbers are the same as in the Access Report. However, if one clicks
"Tools/Analyse with Microsoft Office Excel", the data is exported without
any
addition to the numbers. However, the warning box on each cell says the
"numbers" are, in fact, "text". One can change this by selecting an empty
cell, copying that cell, selecting all the numbers which are "text",
pasting
Special, and under objective, clicking multiply. This converts all the
text
values to number values.

Do you know why this is happening and how I can stop it?
--
Jim


Duane Hookom said:
I'm sorry but I can't make heads or tails of your data. It looks like you
have an un-normalized table but I can't confirm this. Also, it seems that
some rows have 7 values while others have 10.

My suggestion is to simplify your report/query by limiting it to just a
few
records and checking for accuracy. Keep adding records or complexity
until
it breaks. Remember what you did to break it and then find out why.
Click to expand...
Click to expand...
 
Thanks for helping out Larry. I am curious whether the tables are set up
with lookup fields. Perhaps JimTC is seeing looked up values and stored
values which can be confusing.

--
Duane Hookom
MS Access MVP


Larry Linson said:
Access Reports do not arbitrarily add 1 to the numbers provided in their
Record Source. They just do not. Nor do they write anything back to the
Record Source.

The fact that the values all have 1 added when you Export that same Query
to Excel would indicate that the problem is either in the Query or in the
data in the underlying Table itself. I'd say the experimentation you did
with "Tools/Analyse..." would indicate it is probably the Query.

First, open the Table in Table View, and confirm for yourself (and us)
that the numbers are correct, as entered.

Next, open you Report in Design View, click in the upperleftmost little
square, choose Properties, under Data, select RecordSource. If what you
see there is SQL, copy it and post it here. If what you see is the name of
a Query, open that Query in Design View, select SQL View, copy the SQL and
post it here.

Perhaps with that information, someone can assist, or at least suggest
additional information that would help in debugging the problem.

Larry Linson
Microsoft Access MVP


JimTC said:
Dear Duane
The original table lists each patient's code number, the date, the
surgical
service looking after her (Orthopaedics, plastics, gynaecology, etc.),
and
the numeric pain scores (Formatted as number, long integer) for both
resting
and moving for day1, day2, day3, etc. The numeric pain scores are
obtained
by asking the patient to "score" their pain on a scale of 0 through 10,
with
"0" being no pain at all, "5" being moderate pain, and "10" being
unbearable
pain. The query to look at this has 2 extra expressions,
exp1:Year([OperationDate]), and exp2:Month([OperationDate]). In the
criteria
row, I put [Input the year you want], and [Input the month you want].
The
Report is designed with the wizard, based on this query, and summary
only, to
show the average pain scores for each surgical service.
Now, I have discovered what is going wrong, I think. To each pain score
the
Report is adding 1. Thus the number "0" becomes "1", and the number "10"
becomes "11", etc. This is why, on Day1 while moving, the plastic
patient
scores were not 10 and 0, as they are in the query, but 11 and 1 (There
were
only 6 plastic patients during September, and scores were only obtained
while
moving in 2 of these. Unfortunately there are quite a few missing
values)
Thus the Report summary gives the average pain score while moving on Day1
as
6, because it thinks the total is 12. But, obviously, it should read an
average of 5. Similarly with the Day1 at rest Plastic scores. The
numbers
add up to 25, but since Access adds 1 to each number, and there are 6 of
these, it makes the total as 31, and hence the average as 31/6, which is
5.2.
It should calculate 25/6, which is 4.2.
If one "Exports" the query to Excel, all the numbers have 1 added and the
numbers are the same as in the Access Report. However, if one clicks
"Tools/Analyse with Microsoft Office Excel", the data is exported without
any
addition to the numbers. However, the warning box on each cell says the
"numbers" are, in fact, "text". One can change this by selecting an
empty
cell, copying that cell, selecting all the numbers which are "text",
pasting
Special, and under objective, clicking multiply. This converts all the
text
values to number values.

Do you know why this is happening and how I can stop it?
--
Jim


Duane Hookom said:
I'm sorry but I can't make heads or tails of your data. It looks like
you
have an un-normalized table but I can't confirm this. Also, it seems
that
some rows have 7 values while others have 10.

My suggestion is to simplify your report/query by limiting it to just a
few
records and checking for accuracy. Keep adding records or complexity
until
it breaks. Remember what you did to break it and then find out why.

--
Duane Hookom
MS Access MVP


Thankyou. The printout of the report and the query results are as
follows:
EntryDate NHI VAS_AtRest_Day1 VAS_Moving_Day1 VAS_AtRest_Day2
VAS_Moving_Day2 VAS_AtRest_Day3 VAS_Moving_Day3 Service Year Month
14/09/2005 FVM8409 3 7 Orthopaedic 2005 9
01/09/2005 EHK9252 0 9 Orthopaedic 2005 9
15/09/2005 FTQ2513 5 Orthopaedic 2005 9
05/09/2005 ATW5002 0 6 Orthopaedic 2005 9
28/09/2005 SFB2704 4 Plastic 2005 9
26/09/2005 BPX7049 10 10 6 10 0 Plastic 2005 9
28/09/2005 FKR9398 5 Plastic 2005 9
25/09/2005 SEZ5547 5 0 6 Plastic 2005 9
03/09/2005 SEN2114 1 1 Plastic 2005 9
30/09/2005 FTX2015 0 0 Plastic 2005 9
VAS scores By Surgical 9 / 2005
Servic VAS VAS VAS

Day 1
General
(75 detail records)
Avg SumDay1_Resti 228 Resting 3.2 3.3 2.8
SumDay1_Movi 382 Moving 6.0 6.0 5.1
Gynaecology
(10 detail records)
Avg SumDay1_Resti 50 Resting 5.0
SumDay1_Movi 35 Moving 5.0
Obstetric
(71 detail records)
Avg SumDay1_Resti 188 Resting 2.7 1.2
SumDay1_Movi 340 Moving 5.6 4.4
Orthopaedic
(79 detail records)
Avg SumDay1_Resti 213 Resting 2.9 2.8 4.1
SumDay1_Movi 284 Moving 5.2 5.0 5.7
Plastic
(6 detail records)
Avg SumDay1_Resti 31 Resting 5.2 4.5 1.0
SumDay1_Movi 12 Moving 6.0 11.0 7.0
I hope this helps. If not, I can send the original files, if you tell
me
where
--
Jim


:

Numbers in Access reports are nearly always what the designer asks
for. I
expect your query is messed up but it is difficult to determine
without
seeing sample records or your SQL.

--
Duane Hookom
MS Access MVP


I work in a Hospital giving Anesthesia for various operations.
After
their
operation, we measure each patient's pain score (Visual analogue
score-
VAS)
for a few days, to check they have enough analgesia on-board. The
data
is
gathered by our Pain Nurses into an Access Database. I summarise
the
data
monthly, but there are problems. Access doesn't add up the pain
scores
accurately and so gives a false average. For eg: Plastic patients
for
September; 6 patients with scores 10, 5,5,4,0,1 while at rest on
Day1.
Access adds this up to be 31 (and not 25) and gives the average
pain
score
as
5.2 and not 4.2. It gets worse for Day1_Moving. There were only 2
values
recorded, 10 and 0. Yet Access says the sum is 12 and the average
is
6.
What is happening, and how can I stop it. (NB there were many more
Orthopaedic, General, ObsGynae, etc, so I can't import the data
into
Excel)
Click to expand...
Click to expand...
Click to expand...
 
Dear Duane and Larry

Thank you both so much for your help. I have found the answer, which Duane
also alludes to. The Visual Analogue Scores (VAS) were indeed set up with
the Lookup Wizard to ensure the values were only 0 through 10. Of course,
the Autonumber field (Called VAS_ID), which is the value which is saved, is
numbered 1 through 11. I will now change the variables from Autonumber and
Select to a value list containing (0;1;2;3;4;5;6;7;8;9;10) with the variable
as a byte integer. I will then run an update query to substract 1 from all
the values present in the VAS fields.

Thank you both once again
--
Jim


Duane Hookom said:
Thanks for helping out Larry. I am curious whether the tables are set up
with lookup fields. Perhaps JimTC is seeing looked up values and stored
values which can be confusing.

--
Duane Hookom
MS Access MVP


Larry Linson said:
Access Reports do not arbitrarily add 1 to the numbers provided in their
Record Source. They just do not. Nor do they write anything back to the
Record Source.

The fact that the values all have 1 added when you Export that same Query
to Excel would indicate that the problem is either in the Query or in the
data in the underlying Table itself. I'd say the experimentation you did
with "Tools/Analyse..." would indicate it is probably the Query.

First, open the Table in Table View, and confirm for yourself (and us)
that the numbers are correct, as entered.

Next, open you Report in Design View, click in the upperleftmost little
square, choose Properties, under Data, select RecordSource. If what you
see there is SQL, copy it and post it here. If what you see is the name of
a Query, open that Query in Design View, select SQL View, copy the SQL and
post it here.

Perhaps with that information, someone can assist, or at least suggest
additional information that would help in debugging the problem.

Larry Linson
Microsoft Access MVP


JimTC said:
Dear Duane
The original table lists each patient's code number, the date, the
surgical
service looking after her (Orthopaedics, plastics, gynaecology, etc.),
and
the numeric pain scores (Formatted as number, long integer) for both
resting
and moving for day1, day2, day3, etc. The numeric pain scores are
obtained
by asking the patient to "score" their pain on a scale of 0 through 10,
with
"0" being no pain at all, "5" being moderate pain, and "10" being
unbearable
pain. The query to look at this has 2 extra expressions,
exp1:Year([OperationDate]), and exp2:Month([OperationDate]). In the
criteria
row, I put [Input the year you want], and [Input the month you want].
The
Report is designed with the wizard, based on this query, and summary
only, to
show the average pain scores for each surgical service.
Now, I have discovered what is going wrong, I think. To each pain score
the
Report is adding 1. Thus the number "0" becomes "1", and the number "10"
becomes "11", etc. This is why, on Day1 while moving, the plastic
patient
scores were not 10 and 0, as they are in the query, but 11 and 1 (There
were
only 6 plastic patients during September, and scores were only obtained
while
moving in 2 of these. Unfortunately there are quite a few missing
values)
Thus the Report summary gives the average pain score while moving on Day1
as
6, because it thinks the total is 12. But, obviously, it should read an
average of 5. Similarly with the Day1 at rest Plastic scores. The
numbers
add up to 25, but since Access adds 1 to each number, and there are 6 of
these, it makes the total as 31, and hence the average as 31/6, which is
5.2.
It should calculate 25/6, which is 4.2.
If one "Exports" the query to Excel, all the numbers have 1 added and the
numbers are the same as in the Access Report. However, if one clicks
"Tools/Analyse with Microsoft Office Excel", the data is exported without
any
addition to the numbers. However, the warning box on each cell says the
"numbers" are, in fact, "text". One can change this by selecting an
empty
cell, copying that cell, selecting all the numbers which are "text",
pasting
Special, and under objective, clicking multiply. This converts all the
text
values to number values.

Do you know why this is happening and how I can stop it?
--
Jim


:

I'm sorry but I can't make heads or tails of your data. It looks like
you
have an un-normalized table but I can't confirm this. Also, it seems
that
some rows have 7 values while others have 10.

My suggestion is to simplify your report/query by limiting it to just a
few
records and checking for accuracy. Keep adding records or complexity
until
it breaks. Remember what you did to break it and then find out why.

--
Duane Hookom
MS Access MVP


Thankyou. The printout of the report and the query results are as
follows:
EntryDate NHI VAS_AtRest_Day1 VAS_Moving_Day1 VAS_AtRest_Day2
VAS_Moving_Day2 VAS_AtRest_Day3 VAS_Moving_Day3 Service Year Month
14/09/2005 FVM8409 3 7 Orthopaedic 2005 9
01/09/2005 EHK9252 0 9 Orthopaedic 2005 9
15/09/2005 FTQ2513 5 Orthopaedic 2005 9
05/09/2005 ATW5002 0 6 Orthopaedic 2005 9
28/09/2005 SFB2704 4 Plastic 2005 9
26/09/2005 BPX7049 10 10 6 10 0 Plastic 2005 9
28/09/2005 FKR9398 5 Plastic 2005 9
25/09/2005 SEZ5547 5 0 6 Plastic 2005 9
03/09/2005 SEN2114 1 1 Plastic 2005 9
30/09/2005 FTX2015 0 0 Plastic 2005 9
VAS scores By Surgical 9 / 2005
Servic VAS VAS VAS

Day 1
General
(75 detail records)
Avg SumDay1_Resti 228 Resting 3.2 3.3 2.8
SumDay1_Movi 382 Moving 6.0 6.0 5.1
Gynaecology
(10 detail records)
Avg SumDay1_Resti 50 Resting 5.0
SumDay1_Movi 35 Moving 5.0
Obstetric
(71 detail records)
Avg SumDay1_Resti 188 Resting 2.7 1.2
SumDay1_Movi 340 Moving 5.6 4.4
Orthopaedic
(79 detail records)
Avg SumDay1_Resti 213 Resting 2.9 2.8 4.1
SumDay1_Movi 284 Moving 5.2 5.0 5.7
Plastic
(6 detail records)
Avg SumDay1_Resti 31 Resting 5.2 4.5 1.0
SumDay1_Movi 12 Moving 6.0 11.0 7.0
I hope this helps. If not, I can send the original files, if you tell
me
where
--
Jim


:

Numbers in Access reports are nearly always what the designer asks
for. I
expect your query is messed up but it is difficult to determine
without
seeing sample records or your SQL.

--
Duane Hookom
MS Access MVP


I work in a Hospital giving Anesthesia for various operations.
After
their
operation, we measure each patient's pain score (Visual analogue
score-
VAS)
for a few days, to check they have enough analgesia on-board. The
data
is
gathered by our Pain Nurses into an Access Database. I summarise
the
data
monthly, but there are problems. Access doesn't add up the pain
scores
accurately and so gives a false average. For eg: Plastic patients
for
September; 6 patients with scores 10, 5,5,4,0,1 while at rest on
Day1.
Access adds this up to be 31 (and not 25) and gives the average
pain
score
as
5.2 and not 4.2. It gets worse for Day1_Moving. There were only 2
values
recorded, 10 and 0. Yet Access says the sum is 12 and the average
is
6.
What is happening, and how can I stop it. (NB there were many more
Orthopaedic, General, ObsGynae, etc, so I can't import the data
into
Excel)
Click to expand...
Click to expand...
Click to expand...
 
Do yourself a favor and read this link about the evils of lookup fields
http://www.mvps.org/access/lookupfields.htm.

--
Duane Hookom
MS Access MVP


JimTC said:
Dear Duane and Larry

Thank you both so much for your help. I have found the answer, which
Duane
also alludes to. The Visual Analogue Scores (VAS) were indeed set up with
the Lookup Wizard to ensure the values were only 0 through 10. Of course,
the Autonumber field (Called VAS_ID), which is the value which is saved,
is
numbered 1 through 11. I will now change the variables from Autonumber
and
Select to a value list containing (0;1;2;3;4;5;6;7;8;9;10) with the
variable
as a byte integer. I will then run an update query to substract 1 from
all
the values present in the VAS fields.

Thank you both once again
--
Jim


Duane Hookom said:
Thanks for helping out Larry. I am curious whether the tables are set up
with lookup fields. Perhaps JimTC is seeing looked up values and stored
values which can be confusing.

--
Duane Hookom
MS Access MVP


Larry Linson said:
Access Reports do not arbitrarily add 1 to the numbers provided in
their
Record Source. They just do not. Nor do they write anything back to the
Record Source.

The fact that the values all have 1 added when you Export that same
Query
to Excel would indicate that the problem is either in the Query or in
the
data in the underlying Table itself. I'd say the experimentation you
did
with "Tools/Analyse..." would indicate it is probably the Query.

First, open the Table in Table View, and confirm for yourself (and us)
that the numbers are correct, as entered.

Next, open you Report in Design View, click in the upperleftmost little
square, choose Properties, under Data, select RecordSource. If what you
see there is SQL, copy it and post it here. If what you see is the name
of
a Query, open that Query in Design View, select SQL View, copy the SQL
and
post it here.

Perhaps with that information, someone can assist, or at least suggest
additional information that would help in debugging the problem.

Larry Linson
Microsoft Access MVP


Dear Duane
The original table lists each patient's code number, the date, the
surgical
service looking after her (Orthopaedics, plastics, gynaecology, etc.),
and
the numeric pain scores (Formatted as number, long integer) for both
resting
and moving for day1, day2, day3, etc. The numeric pain scores are
obtained
by asking the patient to "score" their pain on a scale of 0 through
10,
with
"0" being no pain at all, "5" being moderate pain, and "10" being
unbearable
pain. The query to look at this has 2 extra expressions,
exp1:Year([OperationDate]), and exp2:Month([OperationDate]). In the
criteria
row, I put [Input the year you want], and [Input the month you want].
The
Report is designed with the wizard, based on this query, and summary
only, to
show the average pain scores for each surgical service.
Now, I have discovered what is going wrong, I think. To each pain
score
the
Report is adding 1. Thus the number "0" becomes "1", and the number
"10"
becomes "11", etc. This is why, on Day1 while moving, the plastic
patient
scores were not 10 and 0, as they are in the query, but 11 and 1
(There
were
only 6 plastic patients during September, and scores were only
obtained
while
moving in 2 of these. Unfortunately there are quite a few missing
values)
Thus the Report summary gives the average pain score while moving on
Day1
as
6, because it thinks the total is 12. But, obviously, it should read
an
average of 5. Similarly with the Day1 at rest Plastic scores. The
numbers
add up to 25, but since Access adds 1 to each number, and there are 6
of
these, it makes the total as 31, and hence the average as 31/6, which
is
5.2.
It should calculate 25/6, which is 4.2.
If one "Exports" the query to Excel, all the numbers have 1 added and
the
numbers are the same as in the Access Report. However, if one clicks
"Tools/Analyse with Microsoft Office Excel", the data is exported
without
any
addition to the numbers. However, the warning box on each cell says
the
"numbers" are, in fact, "text". One can change this by selecting an
empty
cell, copying that cell, selecting all the numbers which are "text",
pasting
Special, and under objective, clicking multiply. This converts all
the
text
values to number values.

Do you know why this is happening and how I can stop it?
--
Jim


:

I'm sorry but I can't make heads or tails of your data. It looks like
you
have an un-normalized table but I can't confirm this. Also, it seems
that
some rows have 7 values while others have 10.

My suggestion is to simplify your report/query by limiting it to just
a
few
records and checking for accuracy. Keep adding records or complexity
until
it breaks. Remember what you did to break it and then find out why.

--
Duane Hookom
MS Access MVP


Thankyou. The printout of the report and the query results are as
follows:
EntryDate NHI VAS_AtRest_Day1 VAS_Moving_Day1 VAS_AtRest_Day2
VAS_Moving_Day2 VAS_AtRest_Day3 VAS_Moving_Day3 Service Year Month
14/09/2005 FVM8409 3 7 Orthopaedic 2005 9
01/09/2005 EHK9252 0 9 Orthopaedic 2005 9
15/09/2005 FTQ2513 5 Orthopaedic 2005 9
05/09/2005 ATW5002 0 6 Orthopaedic 2005 9
28/09/2005 SFB2704 4 Plastic 2005 9
26/09/2005 BPX7049 10 10 6 10 0 Plastic 2005 9
28/09/2005 FKR9398 5 Plastic 2005 9
25/09/2005 SEZ5547 5 0 6 Plastic 2005 9
03/09/2005 SEN2114 1 1 Plastic 2005 9
30/09/2005 FTX2015 0 0 Plastic 2005 9
VAS scores By Surgical 9 / 2005
Servic VAS VAS VAS

Day 1
General
(75 detail records)
Avg SumDay1_Resti 228 Resting 3.2 3.3 2.8
SumDay1_Movi 382 Moving 6.0 6.0 5.1
Gynaecology
(10 detail records)
Avg SumDay1_Resti 50 Resting 5.0
SumDay1_Movi 35 Moving 5.0
Obstetric
(71 detail records)
Avg SumDay1_Resti 188 Resting 2.7 1.2
SumDay1_Movi 340 Moving 5.6 4.4
Orthopaedic
(79 detail records)
Avg SumDay1_Resti 213 Resting 2.9 2.8 4.1
SumDay1_Movi 284 Moving 5.2 5.0 5.7
Plastic
(6 detail records)
Avg SumDay1_Resti 31 Resting 5.2 4.5 1.0
SumDay1_Movi 12 Moving 6.0 11.0 7.0
I hope this helps. If not, I can send the original files, if you
tell
me
where
--
Jim


:

Numbers in Access reports are nearly always what the designer asks
for. I
expect your query is messed up but it is difficult to determine
without
seeing sample records or your SQL.

--
Duane Hookom
MS Access MVP


I work in a Hospital giving Anesthesia for various operations.
After
their
operation, we measure each patient's pain score (Visual analogue
score-
VAS)
for a few days, to check they have enough analgesia on-board.
The
data
is
gathered by our Pain Nurses into an Access Database. I
summarise
the
data
monthly, but there are problems. Access doesn't add up the pain
scores
accurately and so gives a false average. For eg: Plastic
patients
for
September; 6 patients with scores 10, 5,5,4,0,1 while at rest on
Day1.
Access adds this up to be 31 (and not 25) and gives the average
pain
score
as
5.2 and not 4.2. It gets worse for Day1_Moving. There were only
2
values
recorded, 10 and 0. Yet Access says the sum is 12 and the
average
is
6.
What is happening, and how can I stop it. (NB there were many
more
Orthopaedic, General, ObsGynae, etc, so I can't import the data
into
Excel)
Click to expand...
Click to expand...
Click to expand...
 
Nope.

--
Duane Hookom
MS Access MVP
--

Larry Linson said:
Is that the one about "A burden of evil visited on Access users and
developers as punishment for all whose denunciation of Open Source and the
GNU license was insufficiently vigorous?" <G>

Larry
Click to expand...
 
My Access reports are now working and providing accurate summaries. Thank
you once again. Regards, Jim
 
Back
Top