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

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[Joe4]

Cross-post: Why Are Counts Different?

Please note our rules on Cross-Posting. While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered. For a more complete explanation on cross-posting, see here: Forum Rules).

 

[johnywhy]

also posted here
http://www.accessforums.net/queries/why-counts-different-40593.html

 

[xenou]

Perhaps the counts differ because of nulls in some of the fields. These would not be included in a count.

 

[johnywhy]

Perhaps the counts differ because of nulls in some of the fields. These would not be included in a count.

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

<tbody>
</tbody>

here's the query results. Shows 2 CountOfAction:

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

<tbody>
</tbody>

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

thx

 

[xenou]

You should investigate the possibility that the field has a zero length string in it. I would try some tests:

SELECT * FROM TABLE WHERE (TJC = 2720) AND (Action Is Null)

Does it show the two records or only one of them?

 

[johnywhy]

solved. this fixed it:

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

thx!

is there a way to mark the thread 'solved'?

 

[xenou]

Good deal. We don't have the practice of marking threads solved on this forum so it's fine as is.
ξ