OnlineD
01-28-2009, 08:52 AM
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?
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?