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