Counting records across multiple tables

[OnlineD]

I have created a select statement that pulls information out of 3 tables in an MS Access DB and returns the results.

However within one of the tables, Companies, there is also a field called CompanyStatusID which relates to another table in the DB called Company_Status

What I am trying to do is along with returing the results from the select statement below I want to show on the page the count of the results, i.e how many of the entires are companystatusid 1, how many companystatusid 2 etc.

However I also need to show the Company Status text name next to the total corresponding to each id. So the actual displayed result might look like this

Bank = 8
Family Office = 8
Fund of fund (Europe) = 4
HNW = 4

The problem is with the code below the records seem to be getting counted multiple times, therefore giving a false result. I think the solution is to JOIN the tables in some way or use the DISTINCT code but I am not sure how to incorporate this into the statement.


SELECT S.CompanyStatus, COUNT(S.CompanyStatusID)
FROM Companies C, Company_Status S, Subscriptions F
WHERE C.CompanyStatusID = S.CompanyStatusID AND F.FundID = varFundID GROUP BY S.CompanyStatus

Can anyone help?

[Spudhead]

Where does the Subscriptions table come in to it? What joins that to Companies or Company_Status?

[OnlineD]

The subscriptions table is the table contains the FundID, which is the variable I am checking on.

What I am trying to establish is how many, of each type of Company Status appear in the Subscriptions table with the FundID of the variable and who they are.

The Company table stores the Company_Name and CompanyID plus the CompanyStatusID.
Then there is a Company_Status table which stores the various types and is connected to the Company table by the CompanyStatusID
Then there is the Subscriptions table which is connected to the Company table via the CompanyID, and out of this I am trying to pull out all the records that match the FundID variable which is parsed using a form drop down.

[Spudhead]

SELECT S.CompanyStatus, COUNT(S.CompanyStatusID)
FROM Company_Status S
INNER JOIN Companies C ON C.CompanyStatusID = S.CompanyStatusID
INNER JOIN Subscriptions F ON F.CompanyID = C.CompanyID
WHERE F.FundID = varFundID
GROUP BY S.CompanyStatus

What does that do?