Count = 0

[matt330]

I have 11 sites and I'm trying to missed visits at each site.

Currently I'm counting all non-missed visits at the site, and in the report I'm subtracting that number from Total # of patients at the site. This works fine - but there has to be a solution to the more "direct" method described below.

If I count up the actual missed visits and one site does not have any, the result only shows 10 sites and their totals. If the total count of missed visits = 0, is there a way to have access return that result?

I've tried changing the join relationship, inserting IF ([CountOfDay 30 Missed]) Is Null (or is 0) THEN .....

I know my naming conventions suck - I'm learning as I go, and I'm afraid to go back and change things now. Lesson noted for my next DB.

SELECT Count(tbl_Subject_Visit_Tracker.[Day 30 Missed]) AS [CountOfDay 30 Missed], tbl_Subject_Enrollment.[Site #]
FROM (tbl_Site_Demographics INNER JOIN tbl_Subject_Enrollment ON tbl_Site_Demographics.[Site #] = tbl_Subject_Enrollment.[Site #]) LEFT JOIN tbl_Subject_Visit_Tracker ON tbl_Subject_Enrollment.[PID #] = tbl_Subject_Visit_Tracker.[PID #]
WHERE (((tbl_Subject_Visit_Tracker.[Day 30 Missed])=True))

GROUP BY tbl_Subject_Enrollment.[Site #];

 

[FoFa]

Try this:

SELECT Count(tbl_Subject_Visit_Tracker.[Day 30 Missed]) AS [CountOfDay 30 Missed], tbl_Subject_Enrollment.[Site #]
FROM (tbl_Site_Demographics INNER JOIN tbl_Subject_Enrollment ON tbl_Site_Demographics.[Site #] = tbl_Subject_Enrollment.[Site #]) LEFT JOIN tbl_Subject_Visit_Tracker ON tbl_Subject_Enrollment.[PID #] = tbl_Subject_Visit_Tracker.[PID #] AND (((tbl_Subject_Visit_Tracker.[Day 30 Missed])=True))
GROUP BY tbl_Subject_Enrollment.[Site #];
Reply With Quote

 

[matt330]

Thanks. Was the only change to change the "WHERE" to an "AND".

When I tried to run the query like that it gave me the error "join expression not supported", and highlighted "(((tbl_Subject_Visit_Tracker.[Day 30 Missed])=True))"

I changed it back to "WHERE" and it gave me the original result.

 

[FoFa]

Maybe a paren issue, let me reformat it so I can see better:

SELECT Count(tbl_Subject_Visit_Tracker.[Day 30 Missed]) AS [CountOfDay 30 Missed], tbl_Subject_Enrollment.[Site #]

FROM tbl_Site_Demographics 
INNER JOIN tbl_Subject_Enrollment ON tbl_Site_Demographics.[Site #] = tbl_Subject_Enrollment.[Site #] 
LEFT JOIN tbl_Subject_Visit_Tracker ON tbl_Subject_Enrollment.[PID #] = tbl_Subject_Visit_Tracker.[PID #] AND tbl_Subject_Visit_Tracker.[Day 30 Missed]=True
GROUP BY tbl_Subject_Enrollment.[Site #];

You can't put the criteria for a left/right/outer join in the WHERE, or it treats the join as an INNER join, which is your problem. So you have to include it in the join criteria to function properly. Now if it gives you an error on the expression, then maybe the expression is wrong. Is that [Day 30 Missed] field a yes/no field?
Why are you including tbl_Site_Demographics if you are not using anywhere in the query? Why not just FROM tbl_Subject_Enrollment and the left join to tbl_Subject_Visit_Tracker?

 

[matt330]

Good point on the Demographics table, I was going to use that to return a site name but that's not necessary - I took it out. The day 30 missed field is a yes/no field. The intent is that if there isn't any checked for a site, it returns "0" as the number day 30 missed for that site. Is it a problem that it's a yes/no field? The below query again results in a " Join expression not supported with "Day 30 Missed=True" highlighted.

SELECT Count(tbl_Subject_Visit_Tracker.[Day 30 Missed]) AS [CountOfDay 30 Missed], tbl_Subject_Enrollment.[Site #]
FROM tbl_Subject_Enrollment
LEFT JOIN tbl_Subject_Visit_Tracker ON tbl_Subject_Enrollment.[PID #] = tbl_Subject_Visit_Tracker.[PID #] AND tbl_Subject_Visit_Tracker.[Day 30 Missed]=True

GROUP BY tbl_Subject_Enrollment.[Site #];

Thanks for the reply.

 

[FoFa]

Hummm, can't figure out what I am missing. Try this:
SELECT Count(tbl_Subject_Visit_Tracker.*) AS [CountOfDay 30 Missed], tbl_Subject_Enrollment.[Site #]
FROM tbl_Subject_Enrollment
LEFT JOIN tbl_Subject_Visit_Tracker ON tbl_Subject_Enrollment.[PID #] = tbl_Subject_Visit_Tracker.[PID #] AND tbl_Subject_Visit_Tracker.[Day 30 Missed]=True
GROUP BY tbl_Subject_Enrollment.[Site #];

 

[matt330]

Fofa - thanks for your help. The final solution was:

SELECT tbl_Subject_Enrollment.[Site #] AS [Site #], sum(tbl_Subject_Visit_Tracker.[Day 30 Missed])*-1 AS Day30Missed
FROM tbl_Subject_Enrollment LEFT JOIN tbl_Subject_Visit_Tracker ON tbl_Subject_Enrollment.[PID #] = tbl_Subject_Visit_Tracker.[PID #]
GROUP BY tbl_Subject_Enrollment.[Site #];