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