'Count' query not working

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
 
V

vanderghast

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
 
P

PayeDoc

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...
 
J

John Spencer

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...
 
P

PayeDoc

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...
 
J

John Spencer

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem counting 6
Slow query 1
Difficult DMax expression? 2
Difficult query 4
Complicated query 4
DCount 1
Pivot 1
Ranking query 1

Top