R
RFJ
I posted the problem earlier and it was suggested that I include the exact
code.
Essentially, the query counts the number of organisations in bands of annual
turnover (the banding information is contained in a table [Turnover Band].
The rest of the information comes from [1] - a query that contains multiple
records for each organisation.
What I'm trying to do is count the number of organisations in each turnover
band. What my problem query does (code below) is count the number of records
in [1] in each band. (ie If there are 10 records relating to a single
company in [1] then the problem query will count 10 and not 1.)
I do have to run this query off [1] so can SKS (some kind soul <G>) suggest
where the code needs to change to get the answer I want.
TIA
Rob
<code start>
SELECT [Turnover Band].ID, [Turnover Band].[Turnover Description] AS [Annual
Turnover], Count([1].Org_Name) AS [No of Organisations]
FROM 1 INNER JOIN [Turnover Band] ON [1].[Turnover Band] = [Turnover
Band].ID
GROUP BY [Turnover Band].ID, [Turnover Band].[Turnover Description],
[1].[Data-recd]
HAVING ((([1].[Data-recd])=Yes))
ORDER BY [Turnover Band].ID;
<code end>
code.
Essentially, the query counts the number of organisations in bands of annual
turnover (the banding information is contained in a table [Turnover Band].
The rest of the information comes from [1] - a query that contains multiple
records for each organisation.
What I'm trying to do is count the number of organisations in each turnover
band. What my problem query does (code below) is count the number of records
in [1] in each band. (ie If there are 10 records relating to a single
company in [1] then the problem query will count 10 and not 1.)
I do have to run this query off [1] so can SKS (some kind soul <G>) suggest
where the code needs to change to get the answer I want.
TIA
Rob
<code start>
SELECT [Turnover Band].ID, [Turnover Band].[Turnover Description] AS [Annual
Turnover], Count([1].Org_Name) AS [No of Organisations]
FROM 1 INNER JOIN [Turnover Band] ON [1].[Turnover Band] = [Turnover
Band].ID
GROUP BY [Turnover Band].ID, [Turnover Band].[Turnover Description],
[1].[Data-recd]
HAVING ((([1].[Data-recd])=Yes))
ORDER BY [Turnover Band].ID;
<code end>