Conditional Count

[Pam]

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like to
count all of these in one field "Available Spaces" for all non-null values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[Danny J. Lesandrini]

Your new query field would look something like this, assuming your (Blank) is NULL
and not an empty string.

Calc: SUM( IIF([Target 2] = "NA", 0, IIF( Nz([Target1],"") = "", 0, [AvailableSpaces] ) ) )

This says that if Target 2 is NA then sum 0, otherwise, if Target1 is null then sum 0
otherwise sum Available Spaces.

 

[KARL DEWEY]

Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS
AvailableSpaces
FROM YourTable;

 

[Pam]

Danny,
Thank you for the quick reply. I looked at your solution, but am not sure
if it will work. I will have NA's and nulls all over the board. I have 10
Target fields (columns) and 15 rows of data, so I would need the same
expression for all fields. I'm probably missing something, but I don't
understand how this would work.
Thanks,
Pam

Your new query field would look something like this, assuming your (Blank)
is NULL
and not an empty string.

Calc: SUM( IIF([Target 2] = "NA", 0, IIF( Nz([Target1],"") = "", 0,
[AvailableSpaces] ) ) )

This says that if Target 2 is NA then sum 0, otherwise, if Target1 is null
then sum 0
otherwise sum Available Spaces.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like to
count all of these in one field "Available Spaces" for all non-null
values but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution needed. If anyone has an answer, it will be greatly
appreciated.

Thanks in advance,
Pam

 

[Pam]

Karl,

Thanks for the quick reply. I think I need some help tweeking the solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target. I
grouped the data in the query and created a second query based on the first
and entered in SQL view the statement you provided. It returned the correct
answer, but I'm not sure how I can have AvailableSpaces total each row. Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam

Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS
AvailableSpaces
FROM YourTable;

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[KARL DEWEY]

Try this --
SELECT [Target 1], [Target 2], [Target 3], [Target 4], Sum(IIF(Nz([Target
1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1) + IIF(Nz([Target
3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS AvailableSpaces
FROM YourTable;

Karl,

Thanks for the quick reply. I think I need some help tweeking the solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target. I
grouped the data in the query and created a second query based on the first
and entered in SQL view the statement you provided. It returned the correct
answer, but I'm not sure how I can have AvailableSpaces total each row. Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam

Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS
AvailableSpaces
FROM YourTable;

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[Pam]

Karl,

I tried the statement and get an error message of "you tried to execute a
query that does not include the specified expression "SundyneComp" as part
of an aggregrate function". I've included the sql as it appears in my
database. If you would give further guidance, I would certainly appreciate
it.

SELECT tWhiteSpaceMatrixData.SundyneComp, tWhiteSpaceMatrixData.SundynePump,
tWhiteSpaceMatrixData.Sunflo, tWhiteSpaceMatrixData.MasoSine,
tWhiteSpaceMatrixData.Ansimag, tWhiteSpaceMatrixData.HMDKontro,
tWhiteSpaceMatrixData.Marelli,
Sum(IIf(Nz([SundyneComp],0)="N/A",0,1)+IIf(Nz([SundynePump],0)="N/A",0,1)+IIf(Nz([Sunflo],0)="N/A",0,1)+IIf(Nz([Ansimag],0)="N/A",0,1)+IIf(Nz([HMDKontro],0)="N/A",0,1)+IIf(Nz([MasoSine],0)="N/A",0,1)+IIf(Nz([Marelli],0)="N/A",0,1))
AS AvailableSpaces
FROM tWhiteSpaceMatrixData;

Thanks,
Pam

Try this --
SELECT [Target 1], [Target 2], [Target 3], [Target 4], Sum(IIF(Nz([Target
1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1) + IIF(Nz([Target
3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS AvailableSpaces
FROM YourTable;

Karl,

Thanks for the quick reply. I think I need some help tweeking the
solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target. I
grouped the data in the query and created a second query based on the
first
and entered in SQL view the statement you provided. It returned the
correct
answer, but I'm not sure how I can have AvailableSpaces total each row.
Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam

Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS
AvailableSpaces
FROM YourTable;

:

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like
to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[Pam]

Karl,

I got the error message to go away, thanks to post from John Spencer "Any
fields that you aren't using SUM, Avg, etc on must be grouped by". Now, it
just totals all seven fields across for each row - each row for
AvailableSpaces has 7 for the total, regardless of whether it is empty, has
text, or NA.
Can you please help?
Thanks,
Pam

Try this --
SELECT [Target 1], [Target 2], [Target 3], [Target 4], Sum(IIF(Nz([Target
1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1) + IIF(Nz([Target
3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS AvailableSpaces
FROM YourTable;

Karl,

Thanks for the quick reply. I think I need some help tweeking the
solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target. I
grouped the data in the query and created a second query based on the
first
and entered in SQL view the statement you provided. It returned the
correct
answer, but I'm not sure how I can have AvailableSpaces total each row.
Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam

Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS
AvailableSpaces
FROM YourTable;

:

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like
to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[KARL DEWEY]

My mistake --
SELECT tWhiteSpaceMatrixData.SundyneComp, tWhiteSpaceMatrixData.SundynePump,
tWhiteSpaceMatrixData.Sunflo, tWhiteSpaceMatrixData.MasoSine,
tWhiteSpaceMatrixData.Ansimag, tWhiteSpaceMatrixData.HMDKontro,
tWhiteSpaceMatrixData.Marelli, Sum(IIf([SundyneComp]="N/A" OR [SundyneComp]
Is Null,0,1)+ IIf([SundynePump]="N/A" OR [SundynePump] Is Null,0,1)+
IIF([Sunflo]="N/A" OR [Sunflo] Is Null,0,1)+ IIf([Ansimag]="N/A" OR [Ansimag]
Is Null,0,1)+ IIf([HMDKontro] ="N/A" OR [HMDKontro] Is Null,0,1)+
IIf([MasoSine]="N/A" OR [MasoSine] Is Null,0,1)+ IIf([Marelli]="N/A" OR
[Marelli] Is Null,0,1)) AS AvailableSpaces
FROM tWhiteSpaceMatrixData
GROUP BY tWhiteSpaceMatrixData.SundyneComp,
tWhiteSpaceMatrixData.SundynePump, tWhiteSpaceMatrixData.Sunflo,
tWhiteSpaceMatrixData.MasoSine, tWhiteSpaceMatrixData.Ansimag,
tWhiteSpaceMatrixData.HMDKontro, tWhiteSpaceMatrixData.Marelli;

Karl,

I got the error message to go away, thanks to post from John Spencer "Any
fields that you aren't using SUM, Avg, etc on must be grouped by". Now, it
just totals all seven fields across for each row - each row for
AvailableSpaces has 7 for the total, regardless of whether it is empty, has
text, or NA.
Can you please help?
Thanks,
Pam

Try this --
SELECT [Target 1], [Target 2], [Target 3], [Target 4], Sum(IIF(Nz([Target
1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1) + IIF(Nz([Target
3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS AvailableSpaces
FROM YourTable;

Karl,

Thanks for the quick reply. I think I need some help tweeking the
solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target. I
grouped the data in the query and created a second query based on the
first
and entered in SQL view the statement you provided. It returned the
correct
answer, but I'm not sure how I can have AvailableSpaces total each row.
Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam


Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS
AvailableSpaces
FROM YourTable;

:

Hi,

I have a query with several fields such as Target1; Target2; Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would like
to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[Pam]

Karl,

That totaled the records that were already in the query, but I can't update
the query. I deleted all the test records from main table and from subform
table so all records from query were removed and now all the record
selection buttons are greyed out at the bottom and the datasheet rows are
not showing in the subform space.
I went to troubleshoot queries in Help and think maybe this may be part of
problem but I don't know how to resolve it.

Problem: Solution:
Query that calculates a sum, average, count or other type of total on
the values in a field, or an update query that references a field in the
Update To row from either a crosstab query, select query (select query: A
query that asks a question about the data stored in your tables and returns
a result set in the form of a datasheet, without changing the data.), or
subquery (subquery: An SQL SELECT statement that is inside another select or
action query.) that contains totals or aggregate functions By using a domain
aggregate function (domain aggregate function: A function, such as DAvg or
DMax, that is used to calculate statistics over a set of records (a
domain).) in the Update To row of an update query, you can reference fields
from either a crosstab query, select query, or subquery that contains totals
or aggregate functions.



You've been a tremendous help so far and I really thought we had it when the
totals came up at first. If you could again point me in the right direction
and let me know what I'm doing wrong, I would greatly appreciate it. This
particular db is really frustrating me. I know it has a spreadsheet layout
and I probably should use a spreadsheet, but I already have lists of data
that are in the db and if I can get this form to pull that data, it would
make a great tool for the end-user.
Thanks again for your help.
Pam

My mistake --
SELECT tWhiteSpaceMatrixData.SundyneComp,
tWhiteSpaceMatrixData.SundynePump,
tWhiteSpaceMatrixData.Sunflo, tWhiteSpaceMatrixData.MasoSine,
tWhiteSpaceMatrixData.Ansimag, tWhiteSpaceMatrixData.HMDKontro,
tWhiteSpaceMatrixData.Marelli, Sum(IIf([SundyneComp]="N/A" OR
[SundyneComp]
Is Null,0,1)+ IIf([SundynePump]="N/A" OR [SundynePump] Is Null,0,1)+
IIF([Sunflo]="N/A" OR [Sunflo] Is Null,0,1)+ IIf([Ansimag]="N/A" OR
[Ansimag]
Is Null,0,1)+ IIf([HMDKontro] ="N/A" OR [HMDKontro] Is Null,0,1)+
IIf([MasoSine]="N/A" OR [MasoSine] Is Null,0,1)+ IIf([Marelli]="N/A" OR
[Marelli] Is Null,0,1)) AS AvailableSpaces
FROM tWhiteSpaceMatrixData
GROUP BY tWhiteSpaceMatrixData.SundyneComp,
tWhiteSpaceMatrixData.SundynePump, tWhiteSpaceMatrixData.Sunflo,
tWhiteSpaceMatrixData.MasoSine, tWhiteSpaceMatrixData.Ansimag,
tWhiteSpaceMatrixData.HMDKontro, tWhiteSpaceMatrixData.Marelli;

Karl,

I got the error message to go away, thanks to post from John Spencer "Any
fields that you aren't using SUM, Avg, etc on must be grouped by". Now,
it
just totals all seven fields across for each row - each row for
AvailableSpaces has 7 for the total, regardless of whether it is empty,
has
text, or NA.
Can you please help?
Thanks,
Pam

Try this --
SELECT [Target 1], [Target 2], [Target 3], [Target 4],
Sum(IIF(Nz([Target
1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1) + IIF(Nz([Target
3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS AvailableSpaces
FROM YourTable;


:

Karl,

Thanks for the quick reply. I think I need some help tweeking the
solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target.
I
grouped the data in the query and created a second query based on the
first
and entered in SQL view the statement you provided. It returned the
correct
answer, but I'm not sure how I can have AvailableSpaces total each
row.
Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam


Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1))
AS
AvailableSpaces
FROM YourTable;

:

Hi,

I have a query with several fields such as Target1; Target2;
Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would
like
to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam

 

[Pam]

Karl,

Thank you so much for your help. I had to tweek your suggestion, but it
definately steered me in the right direction. This may be the long way
around, but, for now, it's working the way I want.

SELECT tWhiteSpaceMatrixData.MainID, tWhiteSpaceMatrixData.SlspID,
tWhiteSpaceMatrixData.Customer, tWhiteSpaceMatrixData.SundynePump,
IIf([SundynePump]="NA" Or [SundynePump] Is Null,0,1) AS CountSundynePump,
tWhiteSpaceMatrixData.SundyneComp, IIf([SundyneComp]="NA" Or [SundyneComp]
Is Null,0,1) AS CountSundyneComp, tWhiteSpaceMatrixData.Sunflo,
IIf([Sunflo]="NA" Or [Sunflo] Is Null,0,1) AS CountSunflo,
tWhiteSpaceMatrixData.Ansimag, IIf([Ansimag]="NA" Or [Ansimag] Is Null,0,1)
AS CountAnsimag, tWhiteSpaceMatrixData.HMDKontro, IIf([HMDKontro]="NA" Or
[HMDKontro] Is Null,0,1) AS CountHMDKontro, tWhiteSpaceMatrixData.MasoSine,
IIf([MasoSine]="NA" Or [MasoSine] Is Null,0,1) AS CountMasoSine,
tWhiteSpaceMatrixData.Marelli, IIf([Marelli]="NA" Or [Marelli] Is Null,0,1)
AS CountMarelli,
[CountSundynePump]+[CountSundyneComp]+[CountSunflo]+[CountAnsimag]+[CountHMDKontro]+[CountMasoSine]+[CountMarelli]
AS AvailableSpaces
FROM tWhiteSpaceMatrixData;

Thanks again - I really appreciate it!!
Pam

My mistake --
SELECT tWhiteSpaceMatrixData.SundyneComp,
tWhiteSpaceMatrixData.SundynePump,
tWhiteSpaceMatrixData.Sunflo, tWhiteSpaceMatrixData.MasoSine,
tWhiteSpaceMatrixData.Ansimag, tWhiteSpaceMatrixData.HMDKontro,
tWhiteSpaceMatrixData.Marelli, Sum(IIf([SundyneComp]="N/A" OR
[SundyneComp]
Is Null,0,1)+ IIf([SundynePump]="N/A" OR [SundynePump] Is Null,0,1)+
IIF([Sunflo]="N/A" OR [Sunflo] Is Null,0,1)+ IIf([Ansimag]="N/A" OR
[Ansimag]
Is Null,0,1)+ IIf([HMDKontro] ="N/A" OR [HMDKontro] Is Null,0,1)+
IIf([MasoSine]="N/A" OR [MasoSine] Is Null,0,1)+ IIf([Marelli]="N/A" OR
[Marelli] Is Null,0,1)) AS AvailableSpaces
FROM tWhiteSpaceMatrixData
GROUP BY tWhiteSpaceMatrixData.SundyneComp,
tWhiteSpaceMatrixData.SundynePump, tWhiteSpaceMatrixData.Sunflo,
tWhiteSpaceMatrixData.MasoSine, tWhiteSpaceMatrixData.Ansimag,
tWhiteSpaceMatrixData.HMDKontro, tWhiteSpaceMatrixData.Marelli;

Karl,

I got the error message to go away, thanks to post from John Spencer "Any
fields that you aren't using SUM, Avg, etc on must be grouped by". Now,
it
just totals all seven fields across for each row - each row for
AvailableSpaces has 7 for the total, regardless of whether it is empty,
has
text, or NA.
Can you please help?
Thanks,
Pam

Try this --
SELECT [Target 1], [Target 2], [Target 3], [Target 4],
Sum(IIF(Nz([Target
1],0)="N/A",0,1) + IIF(Nz([Target 2],0)="N/A",0,1) + IIF(Nz([Target
3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1)) AS AvailableSpaces
FROM YourTable;


:

Karl,

Thanks for the quick reply. I think I need some help tweeking the
solution
to fit application. I have the first query set with 10 Target fields
(columns) and will have approximately 15 rows of data for each Target.
I
grouped the data in the query and created a second query based on the
first
and entered in SQL view the statement you provided. It returned the
correct
answer, but I'm not sure how I can have AvailableSpaces total each
row.
Is
this possible?
I appreciate your help and if you could instruct me futher, I would
appreciate it.
Thanks,
Pam


Try this --
SELECT Sum(IIF(Nz([Target 1],0)="N/A",0,1) + IIF(Nz([Target
2],0)="N/A",0,1)
+ IIF(Nz([Target 3],0)="N/A",0,1) + IIF(Nz([Target 4],0)="N/A",0,1))
AS
AvailableSpaces
FROM YourTable;

:

Hi,

I have a query with several fields such as Target1; Target2;
Target3;
Target4 with data such as Activity, Goal, $Amts, and NA. I would
like
to
count all of these in one field "Available Spaces" for all non-null
values
but not "NA" values. As below:

Target1 Target 2 Target 3 AvailableSpaces
Activity Goal NA 2
10,000 NA Activity 2
(Blank) Activity NA 1

I've read thru posts and internet searches and can't seem to find
solution
needed. If anyone has an answer, it will be greatly appreciated.

Thanks in advance,
Pam