I'm having a problem that I can't seem to solve. I am using A Master Client Table, which holds all the information of all the clients. I also have many different tables joined to this that have information such as gender,insurance, how they were referred,ect., so that the data can be entered easier.
Right now I have queries set up to count how many clients have each of these things, and if there is no client with one of the insurances or ways of referral it will count a zero.
For example -Insurance Types :3- Independant Health, 3 Blue Cross, 0-Community Blue and 5-Medicare. It counts the zeros fine right now, but if I enter some criteria such as a Date Range or if the person is an active client, it stops reading the zeros and I don't understand why this happens.
Here is my SQL of what I have so far:
SELECT [tblCodes-Insurance].InsuranceCode AS Insurance, Count([tblMaster-Client].ClientID) AS [Count]
FROM [tblCodes-Insurance] LEFT JOIN [tblMaster-Client] ON [tblCodes-Insurance].InsuranceCode = [tblMaster-Client].InsuranceCode
GROUP BY [tblCodes-Insurance].InsuranceCode
ORDER BY [tblCodes-Insurance].InsuranceCode;
Ok - a left join returns all rows from the left table and any matching rows from the right table if they match. However, filtering on the right table means only rows in the left table that match the returned rows in the right table will be returned. There are no matching records in the right table so... you end up with nothing. Follow that?
There aren't really any satisfying ways to deal with this in Access. You need to filter the right table BEFORE joining it to the left table either as a derived table or as a seperate query that you apply the where clause to and join that. SQL Server (if you have access to that) is much better for this.