Why Are Counts Different?

[johnywhy]

i'm confused. i'm doing a simple join with totals. i'm grouping on some fields, counting other fields. i would expect that all the COUNT fields would return identical counts, because they're all counting the same table. but the counts are different. why?

The attached image shows the query in design view. In Units table, each UnitID+TJC combo appears only once. In AggComplianceData, each combo can appear multiple times.



Here's the sql:

PARAMETERS FromDate DateTime, ToDate DateTime;
SELECT AggComplianceData.TJC, Units.UnitName, AggComplianceData.UnitID, AggComplianceData.AHRI, Count(AggComplianceData.Finding) AS CountOfFinding, Count(AggComplianceData.Action) AS CountOfAction, Count(AggComplianceData.TJC) AS CountOfTJC
FROM AggComplianceData INNER JOIN Units ON (Units.TJC = AggComplianceData.TJC) AND (AggComplianceData.UnitID = Units.UnitID)
WHERE (((AggComplianceData.Period) Between [FromDate] And [ToDate]))
GROUP BY AggComplianceData.TJC, Units.UnitName, AggComplianceData.UnitID, AggComplianceData.AHRI;


Here's an excerpt of the results:

TJC	UnitName	UnitID	IntAHRI	CountOfFinding	CountOfAction	CountOfTJC
2720	Acute Rehab	3c-ar	16	3	2	3
2720	Acute Rehab	3c-ar	18	2	2	2
2720	Acute Rehab	3c-ar	58	1	0	1
2720	Acute Rehab	3c-ar	66	1	1	1
2720	Acute Rehab	3c-ar	69	2	2	2
2720	Acute Rehab	3c-ar	71	5	4	5
2720	Acute Rehab	3c-ar	72	1	1	1
2720	Acute Rehab	3c-ar	101	0	2	2
2720	Acute Rehab	3c-ar	200	2	2	2
2720	Bridges INPATIENT	4e-brges	1	1	1	1

Here's the raw AggComplianceData for TJC 2720, UnitID 3c-ar, intAHRI 16:

SurveyID	TJC	UnitID	Period	Finding	AHRI	RootCause	Action	DateDue	IntAHRI
2720.3c-ar.213	2720	3c-ar	2/1/2013	Missing times on some of progress noted.	16				16
2720.3c-ar.613	2720	3c-ar	6/1/2013	Admission progress not not signed; corrected on spot.	16	P&P not followed	Corrected at tiome of survey	6/1/2013	16
2720.3c-ar.713	2720	3c-ar	7/1/2013	Dr. Abadee: Physician orders (multiple) were illegible.	16				16

note, i also posted this question here-
http://www.mrexcel.com/forum/microso...different.html

[lfpm062010]

"NULL" is not counted.

[johnywhy]

"NULL" is not counted.

that's what i thought. but if that's true, then why are we seeing 2 for this item, when there's only 1 non-null?

here's the raw data. there's only one non-null action (for 2720, 3c-ar, AHRI 16)

SurveyID	TJC	UnitID	Period	Finding	AHRI	RootCause	Action	DateDue	IntAHRI
2720.3c-ar.213	2720	3c-ar	2/1/2013	Missing times on some of progress noted.	16				16
2720.3c-ar.613	2720	3c-ar	6/1/2013	Admission progress not not signed; corrected on spot.	16	P&P not followed	Corrected at tiome of survey	6/1/2013	16
2720.3c-ar.713	2720	3c-ar	7/1/2013	Dr. Abadee: Physician orders (multiple) were illegible.	16				16

here's the query results. Shows 2 CountOfAction:

TJC	UnitName	UnitID	IntAHRI	CountOfFinding	CountOfAction	CountOfTJC
2720	Acute Rehab	3c-ar	16	3	2	3

If there's only one Action in the raw data, why is it counting 2?

thx

[lfpm062010]

Can you post this sample database for analysis? It will be easier to debug.

[johnywhy]

unfortunately, i cannot. it's private data. Is the info i provided not sufficient?

[lfpm062010]

I understand. It is just easier with the table structures and data already available. I don't have to recreate them.

Try this query and see what returns.

PARAMETERS FromDate DateTime, ToDate DateTime;

SELECT AggComplianceData.TJC, Units.UnitName, AggComplianceData.UnitID, AggComplianceData.AHRI,
AggComplianceData.Finding, AggComplianceData.Action, AggComplianceData.TJC
FROM AggComplianceData INNER JOIN Units ON (Units.TJC = AggComplianceData.TJC) AND (AggComplianceData.UnitID = Units.UnitID)
WHERE AggComplianceData.Period Between [FromDate] And [ToDate] AND TJC = "2720" AND UnitID = "3c-ar" AND AHRI = 16 and Action IS NOT NULL;

If it return 2 records then, the one of the records that looks like "NULL" is actually BLANK/SPACES. If not, need further analysis.

[johnywhy]

Try this query and see what returns.

i tried it. first it failed because TJC and UnitID in the WHERE clause had to be qualified. I added "AggComplianceData."

now getting

Expression is typed incorrectly, to too complex to be evaluated.

[johnywhy]

If it return 2 records then, the one of the records that looks like "NULL" is actually BLANK/SPACES. If not, need further analysis.

to test this theory, i updated the source table. I set [Action] = Trim([Action]). That should remove any spaces from empty Actions.

 UPDATE AggComplianceData SET AggComplianceData.[Action] = Trim([Action]);

Then i reran the query in my OP. Still getting same results:

TJC	UnitName	UnitID	IntAHRI	CountOfFinding	CountOfAction	CountOfTJC
2720	Acute Rehab	3c-ar	16	3	2	3

[June7]

Trim removes spaces and the result would be an empty string if there are no other characters, not Null. Empty string and Null are not same thing. Empty string will count.

Test with this query:

SELECT * FROM AggComplianceData WHERE Action = "";

Then:

UPDATE AggComplianceData Set Action = Null WHERE Action = "";

[johnywhy]

Success! Great.

i altered your update slightly, to handle embedded spaces:

UPDATE AggComplianceData Set Action = Null WHERE TRIM(Action) = "";

thx!