sum across rows

dhamildog · Apr 19, 2009

I have a patient that gets evaluated at intake, 3mos, 6mos, etc for up to 10
episodes. I have the form and subform working correctly to add evaluation
data, but I cannot get a total score for each episode.

I need to add indicators 1-5 for a particular visit. I have a control in the
footer:
=[IndSA1]+[IndSA2]+[IndSA3]+[IndSA4]+[IndSA5]+[IndSA6]
but I get crazy totals. I need to limit to THIS patient, THIS date and
episode. What am I doing wrong?

It's been a very long time since I have worked in Access and this is
stopping me from moving forward. Thanks for any assistance.

KARL DEWEY · Apr 19, 2009

What am I doing wrong?
You are trying to use Access as a spreadsheet instead of a relational
database.
Collect your data using two tables - patient & evaluations with a
one-to-many relationship on PatientID. Select Referential Integerity and
Cascade Update.
Use a form/subform for patient/evaluations data entry. Set the Master/Child
links using PatientID.
Use a union query to move your current data to the new evaluations table.
SELECT PatientID, EvalDate, IndSA1 AS Data
FROM YourTable
UNION ALL SELECT PatientID, EvalDate, IndSA2 AS Data
FROM YourTable
..........
UNION ALL SELECT PatientID, EvalDate, IndSA6 AS Data
FROM YourTable

To sum you present dat try this --
=Nz([IndSA1],0)+Nz([IndSA2],0)+Nz([IndSA3],0)+Nz([IndSA4],0)+Nz([IndSA5],0)+Nz([IndSA6],0)

dhamildog · Apr 21, 2009

Wow - quick response.

Indeed, I do have my tables set up as suggested. (Remembered that much.) I
did try the equation as you suggested and still get 18 where it should be 5,
etc..... I can see no ryhme or reason for the math. Scores can be 0, 1, or
2 up to a total of 12. Patient can have up to 10 evaluations (10 dates), but
that doesn't seem to be playing into the math, either.
--
dlh

KARL DEWEY said:
You are trying to use Access as a spreadsheet instead of a relational
database.
Collect your data using two tables - patient & evaluations with a
one-to-many relationship on PatientID. Select Referential Integerity and
Cascade Update.
Use a form/subform for patient/evaluations data entry. Set the Master/Child
links using PatientID.
Use a union query to move your current data to the new evaluations table.
SELECT PatientID, EvalDate, IndSA1 AS Data
FROM YourTable
UNION ALL SELECT PatientID, EvalDate, IndSA2 AS Data
FROM YourTable
.........
UNION ALL SELECT PatientID, EvalDate, IndSA6 AS Data
FROM YourTable

To sum you present dat try this --
=Nz([IndSA1],0)+Nz([IndSA2],0)+Nz([IndSA3],0)+Nz([IndSA4],0)+Nz([IndSA5],0)+Nz([IndSA6],0)

dhamildog said:

I have a patient that gets evaluated at intake, 3mos, 6mos, etc for up to 10
episodes. I have the form and subform working correctly to add evaluation
data, but I cannot get a total score for each episode.

I need to add indicators 1-5 for a particular visit. I have a control in the
footer:
=[IndSA1]+[IndSA2]+[IndSA3]+[IndSA4]+[IndSA5]+[IndSA6]
but I get crazy totals. I need to limit to THIS patient, THIS date and
episode. What am I doing wrong?

It's been a very long time since I have worked in Access and this is
stopping me from moving forward. Thanks for any assistance.

Click to expand...

dhamildog · Apr 23, 2009

SELECT DISTINCTROW tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6
ORDER BY tblPatient.PtExtID;

So, patient with PtExtID 999 has a value of 1 for each of the 6 indicators,
the Score would be 6. My query returns 16. Pt #44444 has a total of 8
(1,+2+1+1+2+1) and Score reads 24. Go figure. What's worse is I can
almost see what's wrong, but not quite!
--
dlh

KARL DEWEY said:
Post the complete SQL of your query. Open in design view, click on VIEW - SQL
View, hightlight all, copy, and paste in a post.

dhamildog said:

Wow - quick response.

Indeed, I do have my tables set up as suggested. (Remembered that much.) I
did try the equation as you suggested and still get 18 where it should be 5,
etc..... I can see no ryhme or reason for the math. Scores can be 0, 1, or
2 up to a total of 12. Patient can have up to 10 evaluations (10 dates), but
that doesn't seem to be playing into the math, either.
--
dlh

KARL DEWEY said:

What am I doing wrong?
You are trying to use Access as a spreadsheet instead of a relational
database.
Collect your data using two tables - patient & evaluations with a
one-to-many relationship on PatientID. Select Referential Integerity and
Cascade Update.
Use a form/subform for patient/evaluations data entry. Set the Master/Child
links using PatientID.
Use a union query to move your current data to the new evaluations table.
SELECT PatientID, EvalDate, IndSA1 AS Data
FROM YourTable
UNION ALL SELECT PatientID, EvalDate, IndSA2 AS Data
FROM YourTable
.........
UNION ALL SELECT PatientID, EvalDate, IndSA6 AS Data
FROM YourTable

To sum you present dat try this --
=Nz([IndSA1],0)+Nz([IndSA2],0)+Nz([IndSA3],0)+Nz([IndSA4],0)+Nz([IndSA5],0)+Nz([IndSA6],0)

:

I have a patient that gets evaluated at intake, 3mos, 6mos, etc for up to 10
episodes. I have the form and subform working correctly to add evaluation
data, but I cannot get a total score for each episode.

I need to add indicators 1-5 for a particular visit. I have a control in the
footer:
=[IndSA1]+[IndSA2]+[IndSA3]+[IndSA4]+[IndSA5]+[IndSA6]
but I get crazy totals. I need to limit to THIS patient, THIS date and
episode. What am I doing wrong?

It's been a very long time since I have worked in Access and this is
stopping me from moving forward. Thanks for any assistance.

Click to expand...

Click to expand...

KARL DEWEY · Apr 23, 2009

Try leaving out tblIndSelfAcceptance.IndDate through
tblIndSelfAcceptance.IndSA6 of the SELECT and GROUP BY, only having the
Sum(Nz(... ) AS Score.

dhamildog said:
SELECT DISTINCTROW tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6
ORDER BY tblPatient.PtExtID;

So, patient with PtExtID 999 has a value of 1 for each of the 6 indicators,
the Score would be 6. My query returns 16. Pt #44444 has a total of 8
(1,+2+1+1+2+1) and Score reads 24. Go figure. What's worse is I can
almost see what's wrong, but not quite!
--
dlh

KARL DEWEY said:

Post the complete SQL of your query. Open in design view, click on VIEW - SQL
View, hightlight all, copy, and paste in a post.

dhamildog said:

Wow - quick response.

Indeed, I do have my tables set up as suggested. (Remembered that much.) I
did try the equation as you suggested and still get 18 where it should be 5,
etc..... I can see no ryhme or reason for the math. Scores can be 0, 1, or
2 up to a total of 12. Patient can have up to 10 evaluations (10 dates), but
that doesn't seem to be playing into the math, either.
--
dlh

:

What am I doing wrong?
You are trying to use Access as a spreadsheet instead of a relational
database.
Collect your data using two tables - patient & evaluations with a
one-to-many relationship on PatientID. Select Referential Integerity and
Cascade Update.
Use a form/subform for patient/evaluations data entry. Set the Master/Child
links using PatientID.
Use a union query to move your current data to the new evaluations table.
SELECT PatientID, EvalDate, IndSA1 AS Data
FROM YourTable
UNION ALL SELECT PatientID, EvalDate, IndSA2 AS Data
FROM YourTable
.........
UNION ALL SELECT PatientID, EvalDate, IndSA6 AS Data
FROM YourTable

To sum you present dat try this --
=Nz([IndSA1],0)+Nz([IndSA2],0)+Nz([IndSA3],0)+Nz([IndSA4],0)+Nz([IndSA5],0)+Nz([IndSA6],0)

:

I have a patient that gets evaluated at intake, 3mos, 6mos, etc for up to 10
episodes. I have the form and subform working correctly to add evaluation
data, but I cannot get a total score for each episode.

I need to add indicators 1-5 for a particular visit. I have a control in the
footer:
=[IndSA1]+[IndSA2]+[IndSA3]+[IndSA4]+[IndSA5]+[IndSA6]
but I get crazy totals. I need to limit to THIS patient, THIS date and
episode. What am I doing wrong?

It's been a very long time since I have worked in Access and this is
stopping me from moving forward. Thanks for any assistance.

Click to expand...

Click to expand...

dhamildog · Apr 24, 2009

SELECT DISTINCTROW tblPatient.PtExtID,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID
ORDER BY tblPatient.PtExtID;

And now the total is 39 for the first example! ARGH!
--
dlh

KARL DEWEY said:
Try leaving out tblIndSelfAcceptance.IndDate through
tblIndSelfAcceptance.IndSA6 of the SELECT and GROUP BY, only having the
Sum(Nz(... ) AS Score.

dhamildog said:

SELECT DISTINCTROW tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6
ORDER BY tblPatient.PtExtID;

So, patient with PtExtID 999 has a value of 1 for each of the 6 indicators,
the Score would be 6. My query returns 16. Pt #44444 has a total of 8
(1,+2+1+1+2+1) and Score reads 24. Go figure. What's worse is I can
almost see what's wrong, but not quite!
--
dlh

KARL DEWEY said:

Post the complete SQL of your query. Open in design view, click on VIEW - SQL
View, hightlight all, copy, and paste in a post.

:

Wow - quick response.

Indeed, I do have my tables set up as suggested. (Remembered that much.) I
did try the equation as you suggested and still get 18 where it should be 5,
etc..... I can see no ryhme or reason for the math. Scores can be 0, 1, or
2 up to a total of 12. Patient can have up to 10 evaluations (10 dates), but
that doesn't seem to be playing into the math, either.
--
dlh

:

What am I doing wrong?
You are trying to use Access as a spreadsheet instead of a relational
database.
Collect your data using two tables - patient & evaluations with a
one-to-many relationship on PatientID. Select Referential Integerity and
Cascade Update.
Use a form/subform for patient/evaluations data entry. Set the Master/Child
links using PatientID.
Use a union query to move your current data to the new evaluations table.
SELECT PatientID, EvalDate, IndSA1 AS Data
FROM YourTable
UNION ALL SELECT PatientID, EvalDate, IndSA2 AS Data
FROM YourTable
.........
UNION ALL SELECT PatientID, EvalDate, IndSA6 AS Data
FROM YourTable

To sum you present dat try this --
=Nz([IndSA1],0)+Nz([IndSA2],0)+Nz([IndSA3],0)+Nz([IndSA4],0)+Nz([IndSA5],0)+Nz([IndSA6],0)

:

I have a patient that gets evaluated at intake, 3mos, 6mos, etc for up to 10
episodes. I have the form and subform working correctly to add evaluation
data, but I cannot get a total score for each episode.

I need to add indicators 1-5 for a particular visit. I have a control in the
footer:
=[IndSA1]+[IndSA2]+[IndSA3]+[IndSA4]+[IndSA5]+[IndSA6]
but I get crazy totals. I need to limit to THIS patient, THIS date and
episode. What am I doing wrong?

It's been a very long time since I have worked in Access and this is
stopping me from moving forward. Thanks for any assistance.

Click to expand...

Click to expand...

KARL DEWEY · Apr 24, 2009

Does your tblPatient have any patient listed more than once? Run this query â€“
SELECT tblPatient.PtExtID, Count(tblPatient.PtExtID) AS PatCount
FROM tblPatient
WHERE Count(tblPatient.PtExtID) >1
GROUP BY tblPatient.PtExtID;

Try this --
SELECT tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate
ORDER BY tblPatient.PtExtID;

dhamildog said:
SELECT DISTINCTROW tblPatient.PtExtID,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID
ORDER BY tblPatient.PtExtID;

And now the total is 39 for the first example! ARGH!
--
dlh

KARL DEWEY said:

Try leaving out tblIndSelfAcceptance.IndDate through
tblIndSelfAcceptance.IndSA6 of the SELECT and GROUP BY, only having the
Sum(Nz(... ) AS Score.

dhamildog said:

SELECT DISTINCTROW tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
tblIndSelfAcceptance.Episode, tblIndSelfAcceptance.IndSA1,
tblIndSelfAcceptance.IndSA2, tblIndSelfAcceptance.IndSA3,
tblIndSelfAcceptance.IndSA4, tblIndSelfAcceptance.IndSA5,
tblIndSelfAcceptance.IndSA6
ORDER BY tblPatient.PtExtID;

So, patient with PtExtID 999 has a value of 1 for each of the 6 indicators,
the Score would be 6. My query returns 16. Pt #44444 has a total of 8
(1,+2+1+1+2+1) and Score reads 24. Go figure. What's worse is I can
almost see what's wrong, but not quite!
--
dlh

:

Post the complete SQL of your query. Open in design view, click on VIEW - SQL
View, hightlight all, copy, and paste in a post.

:

Wow - quick response.

Indeed, I do have my tables set up as suggested. (Remembered that much.) I
did try the equation as you suggested and still get 18 where it should be 5,
etc..... I can see no ryhme or reason for the math. Scores can be 0, 1, or
2 up to a total of 12. Patient can have up to 10 evaluations (10 dates), but
that doesn't seem to be playing into the math, either.
--
dlh

:

What am I doing wrong?
You are trying to use Access as a spreadsheet instead of a relational
database.
Collect your data using two tables - patient & evaluations with a
one-to-many relationship on PatientID. Select Referential Integerity and
Cascade Update.
Use a form/subform for patient/evaluations data entry. Set the Master/Child
links using PatientID.
Use a union query to move your current data to the new evaluations table.
SELECT PatientID, EvalDate, IndSA1 AS Data
FROM YourTable
UNION ALL SELECT PatientID, EvalDate, IndSA2 AS Data
FROM YourTable
.........
UNION ALL SELECT PatientID, EvalDate, IndSA6 AS Data
FROM YourTable

To sum you present dat try this --
=Nz([IndSA1],0)+Nz([IndSA2],0)+Nz([IndSA3],0)+Nz([IndSA4],0)+Nz([IndSA5],0)+Nz([IndSA6],0)

:

I have a patient that gets evaluated at intake, 3mos, 6mos, etc for up to 10
episodes. I have the form and subform working correctly to add evaluation
data, but I cannot get a total score for each episode.

I need to add indicators 1-5 for a particular visit. I have a control in the
footer:
=[IndSA1]+[IndSA2]+[IndSA3]+[IndSA4]+[IndSA5]+[IndSA6]
but I get crazy totals. I need to limit to THIS patient, THIS date and
episode. What am I doing wrong?

It's been a very long time since I have worked in Access and this is
stopping me from moving forward. Thanks for any assistance.

Click to expand...

Click to expand...

dhamildog · Apr 24, 2009

No patient listed more than once - primary key is the ExtPtID. and your
suggested query affected no change.
--
dlh

KARL DEWEY said:
Does your tblPatient have any patient listed more than once? Run this query â€“
SELECT tblPatient.PtExtID, Count(tblPatient.PtExtID) AS PatCount
FROM tblPatient
WHERE Count(tblPatient.PtExtID) >1
GROUP BY tblPatient.PtExtID;

Try this --
SELECT tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate
ORDER BY tblPatient.PtExtID;

dhamildog said:

SELECT DISTINCTROW tblPatient.PtExtID,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID
ORDER BY tblPatient.PtExtID;

And now the total is 39 for the first example! ARGH!
--

Click to expand...

KARL DEWEY · Apr 24, 2009

The only thing I can think of is to tear it apart some more until it produces
correct results.
Just start by removing IndSA6, then IndSA5, etc.

Then try build back without the last one removed.

dhamildog said:
No patient listed more than once - primary key is the ExtPtID. and your
suggested query affected no change.
--
dlh

KARL DEWEY said:

Does your tblPatient have any patient listed more than once? Run this query â€“
SELECT tblPatient.PtExtID, Count(tblPatient.PtExtID) AS PatCount
FROM tblPatient
WHERE Count(tblPatient.PtExtID) >1
GROUP BY tblPatient.PtExtID;

Try this --
SELECT tblPatient.PtExtID, tblIndSelfAcceptance.IndDate,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID, tblIndSelfAcceptance.IndDate
ORDER BY tblPatient.PtExtID;

dhamildog said:

SELECT DISTINCTROW tblPatient.PtExtID,
Sum(Nz([tblIndSelfAcceptance]![IndSA1],0)+Nz([tblIndSelfAcceptance]![IndSA2],0)+Nz([tblIndSelfAcceptance]![IndSA3],0)+Nz([tblIndSelfAcceptance]![IndSA4],0)+Nz([tblIndSelfAcceptance]![IndSA5],0)+Nz([tblIndSelfAcceptance]![IndSA6],0))
AS Score
FROM tblPatient LEFT JOIN tblIndSelfAcceptance ON tblPatient.PtExtID =
tblIndSelfAcceptance.PtExtID
GROUP BY tblPatient.PtExtID
ORDER BY tblPatient.PtExtID;

And now the total is 39 for the first example! ARGH!
--

Click to expand...

Click to expand...

dhamildog · Apr 25, 2009

I have really embarrassed myself! Your last post got me thinking about my
form - and my controls for the input fields. I had the wrong column bound,
so I was picking up the ID number! Karl, thank you for all your assistance.
The Nz function is very good for me to know, as a value of 0 is valid in this
evaluation, so that would have come up!
dlh