Query gives wrong Answer - Repost with requested SQL Code

[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>

 

[Gary Walter]

Hi Rob,

Sorry for my ignorance on what SKS means.

Did you try Steve Dassin crosstab method
to get a Distinct count within a group?

TRANSFORM COUNT(*) As CntOrg
SELECT
T.ID,
T.[Turnover Description] AS [Annual Turnover],
COUNT(CntOrg) AS [No of Organisations],
COUNT(*) As TotOrgCnt
FROM
1
INNER JOIN
[Turnover Band] AS T
ON
[1].[Turnover Band] = T.ID
GROUP BY
T.ID, T.[Turnover Description],[1].[Data-recd]
HAVING ((([1].[Data-recd])=Yes))
ORDER BY T.ID
PIVOT 1.Org_Name IN (NULL);

Is there any reason HAVING clause could not
be a WHERE clause?

 

[RFJ]

Hi Gary - no offence taken

I couldn't get that approach to work - but that will be because I'm so low
down on my sql learning curve, so when something doesn't work it's more by
good fortune that I can fix it! (The amazon book arrives tomorrow <G>)

I've reposted another message in the newsgroup where I've got some code to
work to produce a total of all unique records - and I just need help to
produce sub-totals instead of grand total.

Regards,

Robin

Hi Rob,

Sorry for my ignorance on what SKS means.

Did you try Steve Dassin crosstab method
to get a Distinct count within a group?

TRANSFORM COUNT(*) As CntOrg
SELECT
T.ID,
T.[Turnover Description] AS [Annual Turnover],
COUNT(CntOrg) AS [No of Organisations],
COUNT(*) As TotOrgCnt
FROM
1
INNER JOIN
[Turnover Band] AS T
ON
[1].[Turnover Band] = T.ID
GROUP BY
T.ID, T.[Turnover Description],[1].[Data-recd]
HAVING ((([1].[Data-recd])=Yes))
ORDER BY T.ID
PIVOT 1.Org_Name IN (NULL);

Is there any reason HAVING clause could not
be a WHERE clause?

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>

 

[Gary Walter]

Hi Robin,

I'm taking some time off work, so if your
data is not sensitive, could you start a new db,
import the pertinent tables and queries from
your current db, then zip it to me in an attachment?

(whittle the data down if you have to so the zip
file is small...I am on an old telephone line connection)

Just remove "pleasenospam" from my address.

Gary Walter