Problem counting

[Leslie Isaacs]

Hello All

I have a table 'confirmed' with the following fields (amongst others):

[month number]
[client name]
[ni code]

Each [client name] appears on many records, each with a different [month
number], and usually (but not always!) with the same [ni code].
I need a query to tell me which clients (identified by [client name]) have
more than one [ni code], taking into account only those records where [month
number] is >72.

I have tried:

SELECT confirmed.[client name], Count(confirmed.[ni code]) AS [CountOfni
code], confirmed.[ni code]
FROM confirmed
WHERE (((confirmed.[month number])>"71"))
GROUP BY confirmed.[client name], confirmed.[ni code]
HAVING (((Count(confirmed.[ni code]))>1));

.... but this gives me the total number of records for each [client name] and
[ni code] ... which I suppose is what I have asked for, but is not what I
want!

Hope someone can help
Many thanks
Les

 

[Jeff Boyce]

Leslie

Not sure what you mean by 'confirmed'.

If you have the same [client name] (e.g., "Joe Smith") appearing on more
than one record, you have a couple issues. First, you need to move [client
name] to a tblClient, and only insert the ClientID in the record you were
describing (sounded like a one-to-many in relation to client). Second, if
you are using [client name] as your ID, how do you resolve who is who when
you have more than one 'Joe Smith'?

Your description called for records when [month number] > 72, but your SQL
statement used "> 71".

You may wish to take a look at these issues before returning to working out
the query.

 

[Leslie Isaacs]

Hello Jeff

Thanks for your reply.

Sorry I didn't explain it very well!

'confirmed' is the name of the table.
It does not have a key field - it has no need of one.
The [client name] field is in fact related to a corresponding field in a
table called [clients]
Two 'Joe Smith's are handled (very badly, I know) by making one of them J
Smith! (for our purpose this is acceptable - honest!).
72 was a typo - should have been 71.

Does that help?

Thanks again
Les

Leslie

Not sure what you mean by 'confirmed'.

If you have the same [client name] (e.g., "Joe Smith") appearing on more
than one record, you have a couple issues. First, you need to move [client
name] to a tblClient, and only insert the ClientID in the record you were
describing (sounded like a one-to-many in relation to client). Second, if
you are using [client name] as your ID, how do you resolve who is who when
you have more than one 'Joe Smith'?

Your description called for records when [month number] > 72, but your SQL
statement used "> 71".

You may wish to take a look at these issues before returning to working out
the query.

--
Good luck

Jeff Boyce
<Access MVP>

Hello All

I have a table 'confirmed' with the following fields (amongst others):

[month number]
[client name]
[ni code]

Each [client name] appears on many records, each with a different [month
number], and usually (but not always!) with the same [ni code].
I need a query to tell me which clients (identified by [client name]) have
more than one [ni code], taking into account only those records where [month
number] is >72.

I have tried:

SELECT confirmed.[client name], Count(confirmed.[ni code]) AS [CountOfni
code], confirmed.[ni code]
FROM confirmed
WHERE (((confirmed.[month number])>"71"))
GROUP BY confirmed.[client name], confirmed.[ni code]
HAVING (((Count(confirmed.[ni code]))>1));

... but this gives me the total number of records for each [client name] and
[ni code] ... which I suppose is what I have asked for, but is not what I
want!

Hope someone can help
Many thanks
Les

 

[John Spencer (MVP)]

Easiest way is to do this one step at a time. See if each of these queries
gives you the expected results.

QueryOne:
SELECT DISTINCT [Client Name], [Ni Code]
FROM Confirmed
WHERE [Month Number] > "71"

QueryTwo:
SELECT [Client Name]
FROM QueryOne
GROUP BY [Client Name]
HAVING Count([Ni Code]) > 1

QueryThree:
SELECT DISTINCT C.[Client Name], C.[Ni Code]
FROM Confirmed as C INNER JOIN QueryTwo as Q
ON C.[Client Name] = Q.[Client Name]

That could all be done in one combined query in Access 2000 and later. But you
will have to do it in multiple nested queries in Access 97 or earlier.

Post back if you need to do this all in one query statement.

 

[Leslie Isaacs]

John

Thanks for your reply.

Blimey - I'm amazed that it takes 3 queries to do this (in A97).
Many thanks anyway - I will use the queries you have provided.
BTW: (I realise this is off-topic - hope that's OK) I had thought of moving
to A2000 or A2003, but have heard that they would in fact be slower than
A97. Do you know if this is true? If so, given that I'm not aware of any
functionality that I need that isn't in A97 (apart from sometimes being able
to avoid nested queries!), I see no reason to change: do you?

Thanks again
Les

Easiest way is to do this one step at a time. See if each of these queries
gives you the expected results.

QueryOne:
SELECT DISTINCT [Client Name], [Ni Code]
FROM Confirmed
WHERE [Month Number] > "71"

QueryTwo:
SELECT [Client Name]
FROM QueryOne
GROUP BY [Client Name]
HAVING Count([Ni Code]) > 1

QueryThree:
SELECT DISTINCT C.[Client Name], C.[Ni Code]
FROM Confirmed as C INNER JOIN QueryTwo as Q
ON C.[Client Name] = Q.[Client Name]

That could all be done in one combined query in Access 2000 and later. But you
will have to do it in multiple nested queries in Access 97 or earlier.

Post back if you need to do this all in one query statement.

Hello All

I have a table 'confirmed' with the following fields (amongst others):

[month number]
[client name]
[ni code]

Each [client name] appears on many records, each with a different [month
number], and usually (but not always!) with the same [ni code].
I need a query to tell me which clients (identified by [client name]) have
more than one [ni code], taking into account only those records where [month
number] is >72.

I have tried:

SELECT confirmed.[client name], Count(confirmed.[ni code]) AS [CountOfni
code], confirmed.[ni code]
FROM confirmed
WHERE (((confirmed.[month number])>"71"))
GROUP BY confirmed.[client name], confirmed.[ni code]
HAVING (((Count(confirmed.[ni code]))>1));

... but this gives me the total number of records for each [client name] and
[ni code] ... which I suppose is what I have asked for, but is not what I
want!

Hope someone can help
Many thanks
Les

 

[John Spencer (MVP)]

I still use Access 97 in some of my projects. It is stable, it works nicely,
and I've got a lot of experience with it.

I have no idea on the speed issue, although I've "heard" that it is slower. I'm
not sure that it really is and I've never tested.

You could do this in less than 3 queries, probably in one query IF your field
and table names don't require the brackets [] around them. You have spaces in
your field names, so you can't do it in Access 97. There is a syntax that will
work to allow you to put all those queries into one BUT it requires that you not
use any square brackets within the subqueries.

John

Thanks for your reply.

Blimey - I'm amazed that it takes 3 queries to do this (in A97).
Many thanks anyway - I will use the queries you have provided.
BTW: (I realise this is off-topic - hope that's OK) I had thought of moving
to A2000 or A2003, but have heard that they would in fact be slower than
A97. Do you know if this is true? If so, given that I'm not aware of any
functionality that I need that isn't in A97 (apart from sometimes being able
to avoid nested queries!), I see no reason to change: do you?

Thanks again
Les

Easiest way is to do this one step at a time. See if each of these queries
gives you the expected results.

QueryOne:
SELECT DISTINCT [Client Name], [Ni Code]
FROM Confirmed
WHERE [Month Number] > "71"

QueryTwo:
SELECT [Client Name]
FROM QueryOne
GROUP BY [Client Name]
HAVING Count([Ni Code]) > 1

QueryThree:
SELECT DISTINCT C.[Client Name], C.[Ni Code]
FROM Confirmed as C INNER JOIN QueryTwo as Q
ON C.[Client Name] = Q.[Client Name]

That could all be done in one combined query in Access 2000 and later. But you
will have to do it in multiple nested queries in Access 97 or earlier.

Post back if you need to do this all in one query statement.

Hello All

I have a table 'confirmed' with the following fields (amongst others):

[month number]
[client name]
[ni code]

Each [client name] appears on many records, each with a different [month
number], and usually (but not always!) with the same [ni code].
I need a query to tell me which clients (identified by [client name]) have
more than one [ni code], taking into account only those records where [month
number] is >72.

I have tried:

SELECT confirmed.[client name], Count(confirmed.[ni code]) AS [CountOfni
code], confirmed.[ni code]
FROM confirmed
WHERE (((confirmed.[month number])>"71"))
GROUP BY confirmed.[client name], confirmed.[ni code]
HAVING (((Count(confirmed.[ni code]))>1));

... but this gives me the total number of records for each [client name] and
[ni code] ... which I suppose is what I have asked for, but is not what I
want!

Hope someone can help
Many thanks
Les

 

[Leslie Isaacs]

John

Thanks for the further info. I improved my naming convention about 2 years
ago but still have the legacy of my early days!
Without an obvious reason to move from A97 I'll stick with it for now.

Thanks again.
Les

I still use Access 97 in some of my projects. It is stable, it works nicely,
and I've got a lot of experience with it.

I have no idea on the speed issue, although I've "heard" that it is slower. I'm
not sure that it really is and I've never tested.

You could do this in less than 3 queries, probably in one query IF your field
and table names don't require the brackets [] around them. You have spaces in
your field names, so you can't do it in Access 97. There is a syntax that will
work to allow you to put all those queries into one BUT it requires that you not
use any square brackets within the subqueries.

John

Thanks for your reply.

Blimey - I'm amazed that it takes 3 queries to do this (in A97).
Many thanks anyway - I will use the queries you have provided.
BTW: (I realise this is off-topic - hope that's OK) I had thought of moving
to A2000 or A2003, but have heard that they would in fact be slower than
A97. Do you know if this is true? If so, given that I'm not aware of any
functionality that I need that isn't in A97 (apart from sometimes being able
to avoid nested queries!), I see no reason to change: do you?

Thanks again
Les

Easiest way is to do this one step at a time. See if each of these queries
gives you the expected results.

QueryOne:
SELECT DISTINCT [Client Name], [Ni Code]
FROM Confirmed
WHERE [Month Number] > "71"

QueryTwo:
SELECT [Client Name]
FROM QueryOne
GROUP BY [Client Name]
HAVING Count([Ni Code]) > 1

QueryThree:
SELECT DISTINCT C.[Client Name], C.[Ni Code]
FROM Confirmed as C INNER JOIN QueryTwo as Q
ON C.[Client Name] = Q.[Client Name]

That could all be done in one combined query in Access 2000 and later. But you
will have to do it in multiple nested queries in Access 97 or earlier.

Post back if you need to do this all in one query statement.



Leslie Isaacs wrote:

Hello All

I have a table 'confirmed' with the following fields (amongst others):

[month number]
[client name]
[ni code]

Each [client name] appears on many records, each with a different [month
number], and usually (but not always!) with the same [ni code].
I need a query to tell me which clients (identified by [client

name])
have

more than one [ni code], taking into account only those records

where
[month

number] is >72.

I have tried:

SELECT confirmed.[client name], Count(confirmed.[ni code]) AS [CountOfni
code], confirmed.[ni code]
FROM confirmed
WHERE (((confirmed.[month number])>"71"))
GROUP BY confirmed.[client name], confirmed.[ni code]
HAVING (((Count(confirmed.[ni code]))>1));

... but this gives me the total number of records for each [client

name]
and

[ni code] ... which I suppose is what I have asked for, but is not

what
I

want!

Hope someone can help
Many thanks
Les