Convert T/F to Number value & Sum multiple fields for further calc

[Aso]

I have been working on a form that users enter data for various days/times.
What I have managed to do is create fields that will output if particular
fields are null or not and converted those True/False responses into "1" or
"0". I have another field that needs to take the number responses in those
fields and add them up so that ultimately I can take use that total sum to
divide into other data to return an average for that given day. (D1 = Day 1)

[D1PSI1] contains data "499"
[D1PSI2] contains data "500"
[D1PSI3] is blank

[D1t-f1] converts (D1PSI1)"499" to -is not null- and shows a value in the
[D1t-f1] of "1"
[D1t-f2] converts [D1PSI2]"500" to - is not null- and shows a value in the
[D1t-f2] of "1"
[D1t-f3] displays "0" because [D1PSI3] is null.

[SumD1] should calcuate all the "1" values (there are ultimately four fields
per day)

I might be going about this the hard way. I have read through other posters'
questions to try to find a solution, looking at "Nz" and many other
suggestions but to no avail. Any help would be greatly appreciated as I feel
I have wasted so much time on something that I feel should be really simple.
Thank you for your time.

Aso~

 

[James A. Fortune]

I have been working on a form that users enter data for various days/times.
What I have managed to do is create fields that will output if particular
fields are null or not and converted those True/False responses into "1" or
"0". I have another field that needs to take the number responses in those
fields and add them up so that ultimately I can take use that total sum to
divide into other data to return an average for that given day. (D1 = Day 1)

[D1PSI1] contains data "499"
[D1PSI2] contains data "500"
[D1PSI3] is blank

[D1t-f1] converts (D1PSI1)"499" to -is not null- and shows a value in the
[D1t-f1] of "1"
[D1t-f2] converts [D1PSI2]"500" to - is not null- and shows a value in the
[D1t-f2] of "1"
[D1t-f3] displays "0" because [D1PSI3] is null.

[SumD1] should calcuate all the "1" values (there are ultimately four fields
per day)

I might be going about this the hard way. I have read through other posters'
questions to try to find a solution, looking at "Nz" and many other
suggestions but to no avail. Any help would be greatly appreciated as I feel
I have wasted so much time on something that I feel should be really simple.
Thank you for your time.

Aso~

Here are a few expressions to try within a query:

Abs(D1PSI1 IS NOT NULL)

Sum(Abs(D1PSI1 IS NOT NULL))

The four fields should probably go in a separate table with a foreign
key to the main table, along with the value and another field to
indicate to which of the four fields the value refers. I.e., into a
table with a primary key, a foreign key, a field to store the value, and
an indicator. That narrower table structure will actually make it
easier to write the queries you will need, especially when finding
averages. The other fields (containing "f" or "Sum") can likely be
eliminated since a query can calculate them on-the-fly. A Make Table
query can be constructed that will dump your data from the main table
into the new, narrow table.

James A. Fortune
(e-mail address removed)

 

[Aso]

I have been working on a form that users enter data for various days/times.
What I have managed to do is create fields that will output if particular
fields are null or not and converted those True/False responses into "1" or
"0". I have another field that needs to take the number responses in those
fields and add them up so that ultimately I can take use that total sum to
divide into other data to return an average for that given day. (D1 = Day 1)

[D1PSI1] contains data "499"
[D1PSI2] contains data "500"
[D1PSI3] is blank

[D1t-f1] converts (D1PSI1)"499" to -is not null- and shows a value in the
[D1t-f1] of "1"
[D1t-f2] converts [D1PSI2]"500" to - is not null- and shows a value in the
[D1t-f2] of "1"
[D1t-f3] displays "0" because [D1PSI3] is null.

[SumD1] should calcuate all the "1" values (there are ultimately four fields
per day)

I might be going about this the hard way. I have read through other posters'
questions to try to find a solution, looking at "Nz" and many other
suggestions but to no avail. Any help would be greatly appreciated as I feel
I have wasted so much time on something that I feel should be really simple.
Thank you for your time.

Aso~

Here are a few expressions to try within a query:

Abs(D1PSI1 IS NOT NULL)

Sum(Abs(D1PSI1 IS NOT NULL))

The four fields should probably go in a separate table with a foreign
key to the main table, along with the value and another field to
indicate to which of the four fields the value refers. I.e., into a
table with a primary key, a foreign key, a field to store the value, and
an indicator. That narrower table structure will actually make it
easier to write the queries you will need, especially when finding
averages. The other fields (containing "f" or "Sum") can likely be
eliminated since a query can calculate them on-the-fly. A Make Table
query can be constructed that will dump your data from the main table
into the new, narrow table.

James A. Fortune
(e-mail address removed)

Thank you for your response. I am just trying to keep this very simple and I
really have no need to maintain the data. I just need to perform the
calculations to visibly see some numbers and I will most likely recreate the
calculations on a report as well. Do you have any further instructions, or is
this the only way you believe I can make this happen?

Thank you =)

 

[James A. Fortune]

:

I have been working on a form that users enter data for various days/times.
What I have managed to do is create fields that will output if particular
fields are null or not and converted those True/False responses into "1" or
"0". I have another field that needs to take the number responses in those
fields and add them up so that ultimately I can take use that total sum to
divide into other data to return an average for that given day. (D1 = Day 1)

[D1PSI1] contains data "499"
[D1PSI2] contains data "500"
[D1PSI3] is blank

[D1t-f1] converts (D1PSI1)"499" to -is not null- and shows a value in the
[D1t-f1] of "1"
[D1t-f2] converts [D1PSI2]"500" to - is not null- and shows a value in the
[D1t-f2] of "1"
[D1t-f3] displays "0" because [D1PSI3] is null.

[SumD1] should calcuate all the "1" values (there are ultimately four fields
per day)

I might be going about this the hard way. I have read through other posters'
questions to try to find a solution, looking at "Nz" and many other
suggestions but to no avail. Any help would be greatly appreciated as I feel
I have wasted so much time on something that I feel should be really simple.
Thank you for your time.

Aso~

Here are a few expressions to try within a query:

Abs(D1PSI1 IS NOT NULL)

Sum(Abs(D1PSI1 IS NOT NULL))

The four fields should probably go in a separate table with a foreign
key to the main table, along with the value and another field to
indicate to which of the four fields the value refers. I.e., into a
table with a primary key, a foreign key, a field to store the value, and
an indicator. That narrower table structure will actually make it
easier to write the queries you will need, especially when finding
averages. The other fields (containing "f" or "Sum") can likely be
eliminated since a query can calculate them on-the-fly. A Make Table
query can be constructed that will dump your data from the main table
into the new, narrow table.

James A. Fortune
(e-mail address removed)

Thank you for your response. I am just trying to keep this very simple and I
really have no need to maintain the data. I just need to perform the
calculations to visibly see some numbers and I will most likely recreate the
calculations on a report as well. Do you have any further instructions, or is
this the only way you believe I can make this happen?

Thank you =)

Those expressions are pretty close to what you need. The IsNull()
function can be used, but I don't think it's as efficient as 'IS NULL'
in a query. To test the values, try something like:

SELECT D1PSI1, Abs(D1PSI1 IS NOT NULL) AS D1PSTI1NotNullEq1 FROM MyTable;

to see what the expression does.

James A. Fortune
(e-mail address removed)