Need help pls w qry??

[TotallyConfused]

I am having trouble getting accurate results with a cross-tab query. In my
cross tab query I am trying to get a list of unique doc ids with a count of
unique patients to each doc and number of visits. However, when I run my
query I get the same number of patients and numbers of vists and that can't
be because visits do not always = number of patients. Can someone guide me
to how or what is the best way to get the results I need. ex:

Doc ID Doc Name No of Pts No of Visits
12345678 Dr. Jones 3 10

Thank you in advance for any help you can provide.

 

[Lord Kelvan]

you are probably getting a count problem because it is counting the
nulls can you paste your query and a bit of sample data to show us how
it looks

 

[TotallyConfused]

Thank you for responding. Here is the sql. I hope you can help me.


TRANSFORM Count([DOCAUDIT].VISIT_DATE) AS CountOfVISIT_DATE
SELECT [DOCAUDIT].DOC_ID, [DOCAUDIT].DOC_NM,Count([DOCAUDIT].PATIENT_ID) AS
CountOfPATIENT_ID, Count([DOCAUDIT].VISIT_DATE) AS [Total Of VISIT_DATE]
FROM [DOCAUDIT]
GROUP BY [DOCAUDIT].DOC_ID
ORDER BY [DOCAUDIT].DOC_ID
PIVOT [DOCAUDIT].OFFICE_ID;

 

[Lord Kelvan]

can you show a bit of raw data as well so we can see what the count is
counting

 

[TotallyConfused]

I have thousands of rows this is just to give you an idea. Thank you for any
help you can provide.


DOC_ID VISIT_DATE Patient_ID OFC LM OFC SD
12345678 8 8 2 6
98745612 1 1 1
52896314 2 2 2
95135715 2 2 2

 

[John Spencer]

THe SQL you posted can't be your query as it contains a syntax error since you
did not include Doc_NM in the GROUP BY clause.

TRANSFORM Count([DOCAUDIT].VISIT_DATE) AS CountOfVISIT_DATE
SELECT [DOCAUDIT].DOC_ID
, [DOCAUDIT].DOC_NM
, Count([DOCAUDIT].PATIENT_ID) AS CountOfPATIENT_ID
, Count([DOCAUDIT].VISIT_DATE) AS [Total Of VISIT_DATE]
FROM [DOCAUDIT]
GROUP BY [DOCAUDIT].DOC_ID, [DOCAUDIT].DOC_NM
ORDER BY [DOCAUDIT].DOC_ID
PIVOT [DOCAUDIT].OFFICE_ID;

Your problem is that COUNT counts the presence of a value. It does NOT do a
count of unique values. The easiest way to solve your problem may be to use
Allen Browne's ECount function at
http://allenbrowne.com/ser-66.html

Once you have downloaded the function, you can use it in the query with the
following (assuming that DoC_ID is a string value.
ECount("Patient_ID","DocAudit","Doc_id=""" & Doc_ID & """,True)

If Doc_id is a number field then
ECount("Patient_ID","DocAudit","Doc_id=" & Doc_ID,True)

IF that is too slow, post back for an alternative solution where you calculate
the number of patients per doctor using a subquery.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you for responding. Here is the sql. I hope you can help me.


TRANSFORM Count([DOCAUDIT].VISIT_DATE) AS CountOfVISIT_DATE
SELECT [DOCAUDIT].DOC_ID, [DOCAUDIT].DOC_NM,Count([DOCAUDIT].PATIENT_ID) AS
CountOfPATIENT_ID, Count([DOCAUDIT].VISIT_DATE) AS [Total Of VISIT_DATE]
FROM [DOCAUDIT]
GROUP BY [DOCAUDIT].DOC_ID
ORDER BY [DOCAUDIT].DOC_ID
PIVOT [DOCAUDIT].OFFICE_ID;

you are probably getting a count problem because it is counting the
nulls can you paste your query and a bit of sample data to show us how
it looks

 

[Lord Kelvan]

DOC_ID      VISIT_DATE      Patient_ID      OFC LM      OFC SD
12345678        8                       8                 2               6
98745612        1                       1                 1              
52896314        2                       2                 2  
95135715        2                       2                 2

tbh if you want
Doc ID Doc Name No of Pts No of Visits
12345678 Dr. Jones 3 10

as your resualt i dont see why you need a crosstab query

select doc_id,Doc_Name,count(Patient_ID) as [number of pts],
count(VISIT_DATE) as [number of visits]
from DOCAUDIT
group by doc_id,doc_name

or something like that