sum across rows

[dhamildog]

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]

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)

 

[Linq Adams via AccessMonster.com]

Do you ever have Null values in any of your fields? When you add anything and
a Null, you get a Null.

To avoid this use Nz() to convert the Null values to zeros:

=Nz([IndSA1], 0) + Nz([IndSA2], 0) + Nz([IndSA3], 0)...

 

[dhamildog]

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

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.

 

[dhamildog]

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.

 

[KARL DEWEY]

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

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.

 

[dhamildog]

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

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

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.

 

[KARL DEWEY]

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;

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

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

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.

 

[dhamildog]

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

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;

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!
--

 

[KARL DEWEY]

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.

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

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;

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!
--

 

[dhamildog]

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