Query: Different Criteria Query for same record

A

Aaron

I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron





Fld1 Fld2 Fld3
1 Acct#1
1 Acct#2 Acct#8
1 Acct#9
1
1 Acct#3 Acct#10
1 Acct#4 Acct#11
1 Acct#5
1
1 Acct#12
1 Acct#6
1 Acct#7
1

TtlCnt=12 Fld2NullCnt=5 Fld3NullCnt=7
 
B

Brian Camire

You might try a query whose SQL looks something like this:

SELECT
Count([Your Table].[Fld1]) AS TtlCnt,
(Count(*) - Count([Your Table].[Fld2])) AS Fld2NullCnt,
(Count(*) - Count([Your Table].[Fld3])) AS Fld3NullCnt
FROM
[Your Table]

Note that TtlCnt will exclude records were Fld1 is Null. If you want to
count these too, use Count(*) instead of Count([Your Table].[Fld1]).
 
A

Aaron

Thanks for the suggestion. I took some liberty with your
solution and I'm almost there but can't seem to figure out
how to display the record number for the results I'm
counting. The revised SQL is listed below. Thanks again,
I really appreciate the help.


Aaron



SELECT

Count([SomeTable].[FieldOne]) AS TotalRecCnt,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldTwo])))) AS ElecNotNull,

(Count(*)-Count([SomeTable].[FieldTwo])) AS ElecNull,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldThree])))) AS GasNotNull,

(Count(*)-Count([SomeTable].[FieldThree])) AS GasNull

FROM
SomeTable
WHERE
SomeTable.FieldOne = 1056









-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
Count([Your Table].[Fld1]) AS TtlCnt,
(Count(*) - Count([Your Table].[Fld2])) AS Fld2NullCnt,
(Count(*) - Count([Your Table].[Fld3])) AS Fld3NullCnt
FROM
[Your Table]

Note that TtlCnt will exclude records were Fld1 is Null. If you want to
count these too, use Count(*) instead of Count([Your Table].[Fld1]).

Aaron said:
I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron





Fld1 Fld2 Fld3
1 Acct#1
1 Acct#2 Acct#8
1 Acct#9
1
1 Acct#3 Acct#10
1 Acct#4 Acct#11
1 Acct#5
1
1 Acct#12
1 Acct#6
1 Acct#7
1

TtlCnt=12 Fld2NullCnt=5 Fld3NullCnt=7
Click to expand...


.
Click to expand...
 
J

John Vinson

I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron
Click to expand...

Try adding a calculated field for each field you want to count NULLs:

IIF(IsNull([Field2], 1, 0)

and *sum* this value instead of counting.
 
B

Brian Camire

I don't think I understand your question. Specifically:

1. Why are you using

Count([SomeTable].[FieldOne])-(Count(*)-Count([SomeTable].[FieldX]))

instead of just

Count([SomeTable].[FieldX])

to count the number of records with a non-null value in FieldX?

2. What do you mean by "record number"?



Aaron said:
Thanks for the suggestion. I took some liberty with your
solution and I'm almost there but can't seem to figure out
how to display the record number for the results I'm
counting. The revised SQL is listed below. Thanks again,
I really appreciate the help.


Aaron



SELECT

Count([SomeTable].[FieldOne]) AS TotalRecCnt,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldTwo])))) AS ElecNotNull,

(Count(*)-Count([SomeTable].[FieldTwo])) AS ElecNull,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldThree])))) AS GasNotNull,

(Count(*)-Count([SomeTable].[FieldThree])) AS GasNull

FROM
SomeTable
WHERE
SomeTable.FieldOne = 1056









-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
Count([Your Table].[Fld1]) AS TtlCnt,
(Count(*) - Count([Your Table].[Fld2])) AS Fld2NullCnt,
(Count(*) - Count([Your Table].[Fld3])) AS Fld3NullCnt
FROM
[Your Table]

Note that TtlCnt will exclude records were Fld1 is Null. If you want to
count these too, use Count(*) instead of Count([Your Table].[Fld1]).

Aaron said:
I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron





Fld1 Fld2 Fld3
1 Acct#1
1 Acct#2 Acct#8
1 Acct#9
1
1 Acct#3 Acct#10
1 Acct#4 Acct#11
1 Acct#5
1
1 Acct#12
1 Acct#6
1 Acct#7
1

TtlCnt=12 Fld2NullCnt=5 Fld3NullCnt=7
Click to expand...


.
Click to expand...
Click to expand...
 
A

Aaron

Brian

We have two tables where we store customer transactions,
an application table and an item table. The application
table contains the master record while the item table
contains one or more items related to each application
table. We have a process that loads data without a
customer account number after that data is loaded we have
a process that attempts to find the customer account based
on a name and address search. If that fails we end up
doing a manual search for the customer account records.

What I'm trying to do is to keep some statistics on the
number of "Null" records we have after the automated
process has attempted to find a customer's account
number. This will allow us to gauge the effectiveness of
not requiring the customer number up front.

So, in my original table example "Fld1" is the application
number which is repeated in the item table. So, the
statisticas table I want has fields for the application
number, total number of item records, count of electric
are not null, count of gas accounts that are null, count
of gas accounts that are not null and a couple of
additional fields listing the percentage of null and not
null electric and gas accounts per application.

This is why I was using the minus to get the total 100,
null 77, and not null 23.

Aaron


-----Original Message-----
I don't think I understand your question. Specifically:

1. Why are you using

Count([SomeTable].[FieldOne])-(Count(*)-Count([SomeTable]. [FieldX]))

instead of just

Count([SomeTable].[FieldX])

to count the number of records with a non-null value in FieldX?

2. What do you mean by "record number"?



Aaron said:
Thanks for the suggestion. I took some liberty with your
solution and I'm almost there but can't seem to figure out
how to display the record number for the results I'm
counting. The revised SQL is listed below. Thanks again,
I really appreciate the help.


Aaron



SELECT

Count([SomeTable].[FieldOne]) AS TotalRecCnt,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldTwo])))) AS ElecNotNull,

(Count(*)-Count([SomeTable].[FieldTwo])) AS ElecNull,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldThree])))) AS GasNotNull,

(Count(*)-Count([SomeTable].[FieldThree])) AS GasNull

FROM
SomeTable
WHERE
SomeTable.FieldOne = 1056









-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
Count([Your Table].[Fld1]) AS TtlCnt,
(Count(*) - Count([Your Table].[Fld2])) AS Fld2NullCnt,
(Count(*) - Count([Your Table].[Fld3])) AS Fld3NullCnt
FROM
[Your Table]

Note that TtlCnt will exclude records were Fld1 is
Click to expand...
Null.
If you want to
count these too, use Count(*) instead of Count([Your Table].[Fld1]).


I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron





Fld1 Fld2 Fld3
1 Acct#1
1 Acct#2 Acct#8
1 Acct#9
1
1 Acct#3 Acct#10
1 Acct#4 Acct#11
1 Acct#5
1
1 Acct#12
1 Acct#6
1 Acct#7
1

TtlCnt=12 Fld2NullCnt=5 Fld3NullCnt=7


.
Click to expand...
Click to expand...


.
Click to expand...
 
B

Brian Camire

OK, but:

1. If "FieldOne" is never Null, then

Count([SomeTable].[FieldOne])

will equal

Count(*)

so

Count([SomeTable].[FieldOne])-(Count(*)-Count([SomeTable].[FieldX]))

will equal

Count(*)-(Count(*)-Count([SomeTable].[FieldX]))

which is the same as

Count([SomeTable].[FieldX])


2. What did you mean when you said that you "can't seem to figure out how
to display the record number for the results"? What "record number"? The
application number? If so, you might try adding

[SomeTable].[FieldOne]

to the SELECT clause

and

GROUP BY
[Some Table].[FieldOne]

to the end of the SQL statement.



Aaron said:
Brian

We have two tables where we store customer transactions,
an application table and an item table. The application
table contains the master record while the item table
contains one or more items related to each application
table. We have a process that loads data without a
customer account number after that data is loaded we have
a process that attempts to find the customer account based
on a name and address search. If that fails we end up
doing a manual search for the customer account records.

What I'm trying to do is to keep some statistics on the
number of "Null" records we have after the automated
process has attempted to find a customer's account
number. This will allow us to gauge the effectiveness of
not requiring the customer number up front.

So, in my original table example "Fld1" is the application
number which is repeated in the item table. So, the
statisticas table I want has fields for the application
number, total number of item records, count of electric
are not null, count of gas accounts that are null, count
of gas accounts that are not null and a couple of
additional fields listing the percentage of null and not
null electric and gas accounts per application.

This is why I was using the minus to get the total 100,
null 77, and not null 23.

Aaron


-----Original Message-----
I don't think I understand your question. Specifically:

1. Why are you using

Count([SomeTable].[FieldOne])-(Count(*)-Count([SomeTable]. [FieldX]))

instead of just

Count([SomeTable].[FieldX])

to count the number of records with a non-null value in FieldX?

2. What do you mean by "record number"?



Aaron said:
Thanks for the suggestion. I took some liberty with your
solution and I'm almost there but can't seem to figure out
how to display the record number for the results I'm
counting. The revised SQL is listed below. Thanks again,
I really appreciate the help.


Aaron



SELECT

Count([SomeTable].[FieldOne]) AS TotalRecCnt,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldTwo])))) AS ElecNotNull,

(Count(*)-Count([SomeTable].[FieldTwo])) AS ElecNull,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldThree])))) AS GasNotNull,

(Count(*)-Count([SomeTable].[FieldThree])) AS GasNull

FROM
SomeTable
WHERE
SomeTable.FieldOne = 1056










-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
Count([Your Table].[Fld1]) AS TtlCnt,
(Count(*) - Count([Your Table].[Fld2])) AS Fld2NullCnt,
(Count(*) - Count([Your Table].[Fld3])) AS Fld3NullCnt
FROM
[Your Table]

Note that TtlCnt will exclude records were Fld1 is Null.
If you want to
count these too, use Count(*) instead of Count([Your
Table].[Fld1]).


I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron





Fld1 Fld2 Fld3
1 Acct#1
1 Acct#2 Acct#8
1 Acct#9
1
1 Acct#3 Acct#10
1 Acct#4 Acct#11
1 Acct#5
1
1 Acct#12
1 Acct#6
1 Acct#7
1

TtlCnt=12 Fld2NullCnt=5 Fld3NullCnt=7


.
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

Brian,

Thanks. I'm working from home today, but when I get back
in the office tomorrow I'll modify my sql and see if I get
the results I'm expecting. Thanks for all of your help.

Aaron

-----Original Message-----
OK, but:

1. If "FieldOne" is never Null, then

Count([SomeTable].[FieldOne])

will equal

Count(*)

so

Count([SomeTable].[FieldOne])-(Count(*)-Count([SomeTable]. [FieldX]))

will equal

Count(*)-(Count(*)-Count([SomeTable].[FieldX]))

which is the same as

Count([SomeTable].[FieldX])


2. What did you mean when you said that you "can't seem to figure out how
to display the record number for the results"? What "record number"? The
application number? If so, you might try adding

[SomeTable].[FieldOne]

to the SELECT clause

and

GROUP BY
[Some Table].[FieldOne]

to the end of the SQL statement.



Brian

We have two tables where we store customer transactions,
an application table and an item table. The application
table contains the master record while the item table
contains one or more items related to each application
table. We have a process that loads data without a
customer account number after that data is loaded we have
a process that attempts to find the customer account based
on a name and address search. If that fails we end up
doing a manual search for the customer account records.

What I'm trying to do is to keep some statistics on the
number of "Null" records we have after the automated
process has attempted to find a customer's account
number. This will allow us to gauge the effectiveness of
not requiring the customer number up front.

So, in my original table example "Fld1" is the application
number which is repeated in the item table. So, the
statisticas table I want has fields for the application
number, total number of item records, count of electric
are not null, count of gas accounts that are null, count
of gas accounts that are not null and a couple of
additional fields listing the percentage of null and not
null electric and gas accounts per application.

This is why I was using the minus to get the total 100,
null 77, and not null 23.

Aaron


-----Original Message-----
I don't think I understand your question. Specifically:

1. Why are you using

Count([SomeTable].[FieldOne])-(Count(*)-Count
Click to expand...
([SomeTable].
[FieldX]))
instead of just

Count([SomeTable].[FieldX])

to count the number of records with a non-null value in FieldX?

2. What do you mean by "record number"?




Thanks for the suggestion. I took some liberty with your
solution and I'm almost there but can't seem to
Click to expand...
figure
out
how to display the record number for the results I'm
counting. The revised SQL is listed below. Thanks again,
I really appreciate the help.


Aaron



SELECT

Count([SomeTable].[FieldOne]) AS TotalRecCnt,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldTwo])))) AS ElecNotNull,

(Count(*)-Count([SomeTable].[FieldTwo])) AS ElecNull,
(Count([SomeTable].[FieldOne])-((Count(*)-Count
([SomeTable].[FieldThree])))) AS GasNotNull,

(Count(*)-Count([SomeTable].[FieldThree])) AS GasNull

FROM
SomeTable
WHERE
SomeTable.FieldOne = 1056










-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
Count([Your Table].[Fld1]) AS TtlCnt,
(Count(*) - Count([Your Table].[Fld2])) AS Fld2NullCnt,
(Count(*) - Count([Your Table].[Fld3])) AS Fld3NullCnt
FROM
[Your Table]

Note that TtlCnt will exclude records were Fld1 is Null.
If you want to
count these too, use Count(*) instead of Count([Your
Table].[Fld1]).


I have a table with data structured as in the sample
listed below. I know I can get the total counts by
creating three queries; Count Fld1, Count Is Nulls in
Fld2, and Count Is Nulls in Fld3. What I'm wondering is
if there's a way I can generate the count totals in a
single query. Any help would be greatly appreciated.
Thanks in advance.

Aaron





Fld1 Fld2 Fld3
1 Acct#1
1 Acct#2 Acct#8
1 Acct#9
1
1 Acct#3 Acct#10
1 Acct#4 Acct#11
1 Acct#5
1
1 Acct#12
1 Acct#6
1 Acct#7
1

TtlCnt=12 Fld2NullCnt=5 Fld3NullCnt=7


.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query to return only unique records from 2 tables? 2
double entries for each record when running a query 4
Selecting first record from a sorted query 6
Formatting Text for Drop Down-HELP! 4
query producing multiple lines of same data. 4
OleDBdataAdapter in dll for com prob - need updatable query? 2
Need some sample codes for Time Entry databases... 1
query2table 3

Top