'Count' query not working

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

Why isn't this working:

SELECT [x confirmed].practice, [x confirmed].[ni code], Count([x
confirmed].[ni code]) AS [CountOfni code]
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]
HAVING (((Count([x confirmed].[ni code]))>1));

I want it to give a count of the unique values of 'ni code' for each value
of 'practice' in table [x confirmed] - but only where there is more than 1
different 'ni code' for a particular 'practice'.

My attempt above isn't grouping on each 'ni code' (even though I have 'ni
code' in the 'group by' part).

Hope someone can help.

Many thanks
Leslie Isaacs
 
You may do it in two steps, one to get the unique records (no dup), which
can be done with a first GROUP BY (could also be done with a DISTINCT) :



SELECT [x confirmed].practice, [x confirmed].[ni code] As niCode
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]



and then, count the different [ni code]s per practice:



SELECT practice, COUNT(*)
FROM previousQuery
GROUP BY practice
HAVING COUNT(*) > 1



with the HAVING clause keeping only those practices having more than one
different [ni code].




Vanderghast, Access MVP
 
Hello Vanderghast

Many thanks for your help with this: I just didn't think about using two
queries!

Now that I have it working as (I thought!) I wanted, I realise that in fact
it would be very useful if the query returned, for those cases where there
is more than one 'ni code' value, the actual value itself and the number of
occurrences of that value. So whereas the 2nd query in your solution returns
one row per 'practice' with more than one 'ni code', the new query (if it's
possible) would return one row for each of the 'ni code' values of those
'practices' with more than one 'ni code'.

I think I can see how to do this with a 3rd query based on your 2nd query:
or can it be done in a modified 2nd query?

Many thanks for your continued help.
Leslie Isaacs


vanderghast said:
You may do it in two steps, one to get the unique records (no dup), which
can be done with a first GROUP BY (could also be done with a DISTINCT) :



SELECT [x confirmed].practice, [x confirmed].[ni code] As niCode
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]



and then, count the different [ni code]s per practice:



SELECT practice, COUNT(*)
FROM previousQuery
GROUP BY practice
HAVING COUNT(*) > 1



with the HAVING clause keeping only those practices having more than one
different [ni code].




Vanderghast, Access MVP


PayeDoc said:
Hello All

Why isn't this working:

SELECT [x confirmed].practice, [x confirmed].[ni code], Count([x
confirmed].[ni code]) AS [CountOfni code]
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]
HAVING (((Count([x confirmed].[ni code]))>1));

I want it to give a count of the unique values of 'ni code' for each value
of 'practice' in table [x confirmed] - but only where there is more than 1
different 'ni code' for a particular 'practice'.

My attempt above isn't grouping on each 'ni code' (even though I have 'ni
code' in the 'group by' part).

Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
First Query: qTwoOrMore
SELECT [x confirmed].practice
FROM months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice
HAVING Max(Ni Code] <> Min(NI Code]

Second Query
SELECT [x confirmed].practice
, [x confirmed].[ni code]
, Count([x confirmed].[ni code]) AS [CountOfni code]
FROM (months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name]
INNER JOIN qTwoOrMore
ON [x confirmed].practice = qTwoOrMore.Practice
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello Vanderghast

Many thanks for your help with this: I just didn't think about using two
queries!

Now that I have it working as (I thought!) I wanted, I realise that in fact
it would be very useful if the query returned, for those cases where there
is more than one 'ni code' value, the actual value itself and the number of
occurrences of that value. So whereas the 2nd query in your solution returns
one row per 'practice' with more than one 'ni code', the new query (if it's
possible) would return one row for each of the 'ni code' values of those
'practices' with more than one 'ni code'.

I think I can see how to do this with a 3rd query based on your 2nd query:
or can it be done in a modified 2nd query?

Many thanks for your continued help.
Leslie Isaacs


vanderghast said:
You may do it in two steps, one to get the unique records (no dup), which
can be done with a first GROUP BY (could also be done with a DISTINCT) :



SELECT [x confirmed].practice, [x confirmed].[ni code] As niCode
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]



and then, count the different [ni code]s per practice:



SELECT practice, COUNT(*)
FROM previousQuery
GROUP BY practice
HAVING COUNT(*) > 1



with the HAVING clause keeping only those practices having more than one
different [ni code].




Vanderghast, Access MVP


PayeDoc said:
Hello All

Why isn't this working:

SELECT [x confirmed].practice, [x confirmed].[ni code], Count([x
confirmed].[ni code]) AS [CountOfni code]
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]
HAVING (((Count([x confirmed].[ni code]))>1));

I want it to give a count of the unique values of 'ni code' for each value
of 'practice' in table [x confirmed] - but only where there is more than 1
different 'ni code' for a particular 'practice'.

My attempt above isn't grouping on each 'ni code' (even though I have 'ni
code' in the 'group by' part).

Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Hello John

Many thanks for your help with this.

I have tried the queries you suggested but am getting a syntax error in the
JOIN expression on the 2nd query. I tried removing the open bracket between
FROM and months (because I couldn't see the corresponding close bracket),
but to no avail. I have amended
HAVING Max(Ni Code] <> Min(NI Code]
to
HAVING Max([Ni Code]) <> Min([NI Code])
in the 1st query - presumably this is correct?

I can't see what's wrong with the 2nd query though.

Thanks again for your help.
Les



John Spencer said:
First Query: qTwoOrMore
SELECT [x confirmed].practice
FROM months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice
HAVING Max(Ni Code] <> Min(NI Code]

Second Query
SELECT [x confirmed].practice
, [x confirmed].[ni code]
, Count([x confirmed].[ni code]) AS [CountOfni code]
FROM (months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name]
INNER JOIN qTwoOrMore
ON [x confirmed].practice = qTwoOrMore.Practice
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello Vanderghast

Many thanks for your help with this: I just didn't think about using two
queries!

Now that I have it working as (I thought!) I wanted, I realise that in fact
it would be very useful if the query returned, for those cases where there
is more than one 'ni code' value, the actual value itself and the number of
occurrences of that value. So whereas the 2nd query in your solution returns
one row per 'practice' with more than one 'ni code', the new query (if it's
possible) would return one row for each of the 'ni code' values of those
'practices' with more than one 'ni code'.

I think I can see how to do this with a 3rd query based on your 2nd query:
or can it be done in a modified 2nd query?

Many thanks for your continued help.
Leslie Isaacs


vanderghast said:
You may do it in two steps, one to get the unique records (no dup), which
can be done with a first GROUP BY (could also be done with a DISTINCT) :



SELECT [x confirmed].practice, [x confirmed].[ni code] As niCode
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]



and then, count the different [ni code]s per practice:



SELECT practice, COUNT(*)
FROM previousQuery
GROUP BY practice
HAVING COUNT(*) > 1



with the HAVING clause keeping only those practices having more than one
different [ni code].




Vanderghast, Access MVP


Hello All

Why isn't this working:

SELECT [x confirmed].practice, [x confirmed].[ni code], Count([x
confirmed].[ni code]) AS [CountOfni code]
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]
HAVING (((Count([x confirmed].[ni code]))>1));

I want it to give a count of the unique values of 'ni code' for each value
of 'practice' in table [x confirmed] - but only where there is more
Click to expand...
than
1
different 'ni code' for a particular 'practice'.

My attempt above isn't grouping on each 'ni code' (even though I have 'ni
code' in the 'group by' part).

Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Here is the query stripped of unneeded parentheses (Access will put them back).

I did miss one in the from clause. Access SQL insists on having parentheses
around JOIN phrases if there are more than two tables involved in the FROM clause.

Try this one.
Second Query
SELECT [x confirmed].practice
, [x confirmed].[ni code]
, Count([x confirmed].[ni code]) AS [CountOfni code]
FROM

(months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name])

INNER JOIN qTwoOrMore
ON [x confirmed].practice = qTwoOrMore.Practice

WHERE months.year=[Forms]![frm x main]![year]
GROUP BY [x confirmed].practice, [x confirmed].[ni code]

If you have further problems, you might consider starting a new thread. I am
going to be offline for the next few days and will not be responding.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello John

Many thanks for your help with this.

I have tried the queries you suggested but am getting a syntax error in the
JOIN expression on the 2nd query. I tried removing the open bracket between
FROM and months (because I couldn't see the corresponding close bracket),
but to no avail. I have amended
HAVING Max(Ni Code] <> Min(NI Code]
to
HAVING Max([Ni Code]) <> Min([NI Code])
in the 1st query - presumably this is correct?

I can't see what's wrong with the 2nd query though.

Thanks again for your help.
Les



John Spencer said:
First Query: qTwoOrMore
SELECT [x confirmed].practice
FROM months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice
HAVING Max(Ni Code] <> Min(NI Code]

Second Query
SELECT [x confirmed].practice
, [x confirmed].[ni code]
, Count([x confirmed].[ni code]) AS [CountOfni code]
FROM (months INNER JOIN [x confirmed]
ON months.[month name] = [x confirmed].[month name]
INNER JOIN qTwoOrMore
ON [x confirmed].practice = qTwoOrMore.Practice
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello Vanderghast

Many thanks for your help with this: I just didn't think about using two
queries!

Now that I have it working as (I thought!) I wanted, I realise that in fact
it would be very useful if the query returned, for those cases where there
is more than one 'ni code' value, the actual value itself and the number of
occurrences of that value. So whereas the 2nd query in your solution returns
one row per 'practice' with more than one 'ni code', the new query (if it's
possible) would return one row for each of the 'ni code' values of those
'practices' with more than one 'ni code'.

I think I can see how to do this with a 3rd query based on your 2nd query:
or can it be done in a modified 2nd query?

Many thanks for your continued help.
Leslie Isaacs


You may do it in two steps, one to get the unique records (no dup), which
can be done with a first GROUP BY (could also be done with a DISTINCT) :


SELECT [x confirmed].practice, [x confirmed].[ni code] As niCode
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]



and then, count the different [ni code]s per practice:



SELECT practice, COUNT(*)
FROM previousQuery
GROUP BY practice
HAVING COUNT(*) > 1



with the HAVING clause keeping only those practices having more than one
different [ni code].




Vanderghast, Access MVP


Hello All

Why isn't this working:

SELECT [x confirmed].practice, [x confirmed].[ni code], Count([x
confirmed].[ni code]) AS [CountOfni code]
FROM months INNER JOIN [x confirmed] ON months.[month name] = [x
confirmed].[month name]
WHERE (((months.year)=[Forms]![frm x main]![year]))
GROUP BY [x confirmed].practice, [x confirmed].[ni code]
HAVING (((Count([x confirmed].[ni code]))>1));

I want it to give a count of the unique values of 'ni code' for each
value
of 'practice' in table [x confirmed] - but only where there is more than
1
different 'ni code' for a particular 'practice'.

My attempt above isn't grouping on each 'ni code' (even though I have
'ni
code' in the 'group by' part).

Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Back
Top