Do not Count Duplicates

[bdehning]

I have a query based on two joined tables and need to know how to insert SQL into existing sQL so my query counts "Assigned Consultant" Properly. if the following

BOB
Bob
Glen
Glen
SAm

I only want the countofAssigned Consultant to come out as three in my report based on the total query and not 5.

This is the query without the field for count of Assigned consultant that was giving me the duplicating count problem.

Help, place the following

"SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])" into the following SQL so that each consultant for each record is only counted once.

I need help in placing into this existing SQL.

SELECT DISTINCT [Account Information].[Policy Number], [Account Information].[Account Name], [Account Information].EAP, [Account Information].[Class Code], [Account Information].[Expiration Date], [Account Information].[Service Frequency], [Account Information].[Nature of Operations], Sum([Account Information].EAP) AS
SumOfEAP, [Account Information].[X-Mod], Location.[Assigned Consultant], Location.[Location Servicing Division]
FROM [Account Information] LEFT JOIN Location ON [Account Information].[Policy Number] = Location.[Policy Number]
GROUP BY [Account Information].[Policy Number], [Account Information].[Account Name], [Account Information].EAP, [Account Information].[Class Code], [Account Information].[Expiration Date], [Account Information].[Service Frequency], [Account Information].[Nature of Operations], [Account Information].[X-Mod], Location.[Assigned Consultant], Location.[Location Servicing Division];

I appreciate your efforts but am a rookie at sql's and how they work. SO please help with the code.

 

[Tom Ellison]

Dear B:

Here's your starting query with a bit of modification:

SELECT DISTINCT A.[Policy Number], A.[Account Name], A.EAP,
A.[Class Code], A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], Sum(A.EAP) AS SumOfEAP, A.[X-Mod],
L.[Assigned Consultant], L.[Location Servicing Division]
FROM [Account Information] A
LEFT JOIN Location L ON L.[Policy Number] = A.[Policy Number]
GROUP BY A.[Policy Number], A.[Account Name], A.EAP, A.[Class Code],
A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], A.[X-Mod], L.[Assigned Consultant],
L.[Location Servicing Division];

I have never seen a query that shows the value of a column, and at the
same time sums that value. You have that for the EAP column. I don't
know what effect you want there, but it seems odd. The sum would be
across all the columns with identical values in all the columns in
your query, including EAP. When you run this, are there any cases
where EAP and SumOfEAP are different. It is possible, but would
probably be rare, and it is unusual that such a sum would be
desirable.

Now, down to finding the number of assigned consultants. I'm going to
use a correlated subquery for this. First, I copy all your SELECT
list, up to the FROM. Then I insert the new column, then the rest of
your query.

SELECT DISTINCT A.[Policy Number], A.[Account Name], A.EAP,
A.[Class Code], A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], Sum(A.EAP) AS SumOfEAP, A.[X-Mod],
L.[Assigned Consultant], L.[Location Servicing Division],
(SELECT COUNT(*) FROM (SELECT DISTINCT [Assigned Consultant]
FROM [Account Information] A1
WHERE A1.[Policy Number] = A.[Policy Number]))
AS CountConsultants
FROM [Account Information] A
LEFT JOIN Location L ON L.[Policy Number] = A.[Policy Number]
GROUP BY A.[Policy Number], A.[Account Name], A.EAP, A.[Class Code],
A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], A.[X-Mod], L.[Assigned Consultant],
L.[Location Servicing Division];

The above is perfectly good SQL. Unfortunately, Jet won't run
prefectly good SQL, and you're probably running Jet. If you're
running MSDE instead, stick with the above.

The specific problem is that I had to nest subqueries two levels, and
the inner level references the outer query. Jet USUALLY won't handle
this, so we need a work-around.

Create another query, I'll call CountConsultants:

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]))
GROUP BY [Policy Number]

Here is the result for every policy, right? Please check it.

Now you can join this query to the one you had before using [Policy
Number] and get what you wanted. Make sense?

Sorry I had to go at the round-about way. I have to write queries the
way I expect them to work, then add the work-around for Jet
limitations. These limitations always make me mad, but everyone
already knows I'm mad. Can't you tell?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a query based on two joined tables and need to know how to insert SQL into existing sQL so my query counts "Assigned Consultant" Properly. if the following

BOB
Bob
Glen
Glen
SAm

I only want the countofAssigned Consultant to come out as three in my report based on the total query and not 5.

This is the query without the field for count of Assigned consultant that was giving me the duplicating count problem.

Help, place the following

"SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])" into the following SQL so that each consultant for each record is only counted once.

I need help in placing into this existing SQL.

SELECT DISTINCT [Account Information].[Policy Number], [Account Information].[Account Name], [Account Information].EAP, [Account Information].[Class Code], [Account Information].[Expiration Date], [Account Information].[Service Frequency], [Account Information].[Nature of Operations], Sum([Account Information].EAP) AS
SumOfEAP, [Account Information].[X-Mod], Location.[Assigned Consultant], Location.[Location Servicing Division]
FROM [Account Information] LEFT JOIN Location ON [Account Information].[Policy Number] = Location.[Policy Number]
GROUP BY [Account Information].[Policy Number], [Account Information].[Account Name], [Account Information].EAP, [Account Information].[Class Code], [Account Information].[Expiration Date], [Account Information].[Service Frequency], [Account Information].[Nature of Operations], [Account Information].[X-Mod], Location.[Assigned Consultant], Location.[Location Servicing Division];

I appreciate your efforts but am a rookie at sql's and how they work. SO please help with the code.

 

[bdehning]

Tom the modification worked but the last two when run both give me "Syntax Error in From Clause" and I can't save either one to try and connect.

I appreciate the help so far but I have been having trouble trying to get this issue resolved. Can you continue to help.

Thanks
--
Brian

Dear B:

Here's your starting query with a bit of modification:

SELECT DISTINCT A.[Policy Number], A.[Account Name], A.EAP,
A.[Class Code], A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], Sum(A.EAP) AS SumOfEAP, A.[X-Mod],
L.[Assigned Consultant], L.[Location Servicing Division]
FROM [Account Information] A
LEFT JOIN Location L ON L.[Policy Number] = A.[Policy Number]
GROUP BY A.[Policy Number], A.[Account Name], A.EAP, A.[Class Code],
A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], A.[X-Mod], L.[Assigned Consultant],
L.[Location Servicing Division];

I have never seen a query that shows the value of a column, and at the
same time sums that value. You have that for the EAP column. I don't
know what effect you want there, but it seems odd. The sum would be
across all the columns with identical values in all the columns in
your query, including EAP. When you run this, are there any cases
where EAP and SumOfEAP are different. It is possible, but would
probably be rare, and it is unusual that such a sum would be
desirable.

Now, down to finding the number of assigned consultants. I'm going to
use a correlated subquery for this. First, I copy all your SELECT
list, up to the FROM. Then I insert the new column, then the rest of
your query.

SELECT DISTINCT A.[Policy Number], A.[Account Name], A.EAP,
A.[Class Code], A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], Sum(A.EAP) AS SumOfEAP, A.[X-Mod],
L.[Assigned Consultant], L.[Location Servicing Division],
(SELECT COUNT(*) FROM (SELECT DISTINCT [Assigned Consultant]
FROM [Account Information] A1
WHERE A1.[Policy Number] = A.[Policy Number]))
AS CountConsultants
FROM [Account Information] A
LEFT JOIN Location L ON L.[Policy Number] = A.[Policy Number]
GROUP BY A.[Policy Number], A.[Account Name], A.EAP, A.[Class Code],
A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], A.[X-Mod], L.[Assigned Consultant],
L.[Location Servicing Division];

The above is perfectly good SQL. Unfortunately, Jet won't run
prefectly good SQL, and you're probably running Jet. If you're
running MSDE instead, stick with the above.

The specific problem is that I had to nest subqueries two levels, and
the inner level references the outer query. Jet USUALLY won't handle
this, so we need a work-around.

Create another query, I'll call CountConsultants:

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]))
GROUP BY [Policy Number]

Here is the result for every policy, right? Please check it.

Now you can join this query to the one you had before using [Policy
Number] and get what you wanted. Make sense?

Sorry I had to go at the round-about way. I have to write queries the
way I expect them to work, then add the work-around for Jet
limitations. These limitations always make me mad, but everyone
already knows I'm mad. Can't you tell?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a query based on two joined tables and need to know how to insert SQL into existing sQL so my query counts "Assigned Consultant" Properly. if the following

BOB
Bob
Glen
Glen
SAm

I only want the countofAssigned Consultant to come out as three in my report based on the total query and not 5.

This is the query without the field for count of Assigned consultant that was giving me the duplicating count problem.

Help, place the following

"SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])" into the following SQL so that each consultant for each record is only counted once.

I need help in placing into this existing SQL.

SELECT DISTINCT [Account Information].[Policy Number], [Account Information].[Account Name], [Account Information].EAP, [Account Information].[Class Code], [Account Information].[Expiration Date], [Account Information].[Service Frequency], [Account Information].[Nature of Operations], Sum([Account Information].EAP) AS
SumOfEAP, [Account Information].[X-Mod], Location.[Assigned Consultant], Location.[Location Servicing Division]
FROM [Account Information] LEFT JOIN Location ON [Account Information].[Policy Number] = Location.[Policy Number]
GROUP BY [Account Information].[Policy Number], [Account Information].[Account Name], [Account Information].EAP, [Account Information].[Class Code], [Account Information].[Expiration Date], [Account Information].[Service Frequency], [Account Information].[Nature of Operations], [Account Information].[X-Mod], Location.[Assigned Consultant], Location.[Location Servicing Division];

I appreciate your efforts but am a rookie at sql's and how they work. SO please help with the code.

 

[Tom Ellison]

This one had too many closing parens:

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information])
GROUP BY [Policy Number]

Now how is it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[bdehning]

Tom, same error with From clause still.

Maybe I am making this too hard, so I try to explain it better because I think it should be simpler.

I have table [Account Information]which allows no duplicates for "Policy Number" and is joined like you know from code I pasted to [Location] by "Policy Number". This allows there to be many locations for each policy number. The field I am trying to count "Assigned Consultant" is located in the [Location] table so if there are 3 locations for the same policy number the assigned consultant is shown for each record.

What I am trying to do ultimately is in a report being able to show the count for the assigned consulant to be 1 in this case and not 3 as it does currently. I have the report correctly only showing the accounts one time but I need to restrict the count so that I do not get 3 but 1. Is there something simple to do at the report level to restrict what gets counted?


--
Brian

This one had too many closing parens:

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information])
GROUP BY [Policy Number]

Now how is it?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom the modification worked but the last two when run both give me "Syntax Error in From Clause" and I can't save either one to try and connect.

I appreciate the help so far but I have been having trouble trying to get this issue resolved. Can you continue to help.

Thanks

 

[Tom Ellison]

Must have been having a bad day. An alias is required on a subquery
in a FROM clause:

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X
GROUP BY [Policy Number]

Are the values given by this query the ones to be put into your
finished product?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[bdehning]

When I try the below SQL I still get syntax eror in FROM clause.

If I can get the count right I use a text box in the report to show the number by using =Sum([CountOfASSIGNED CONSULTANT]) for control source from my original query.

I need reports to count correctly the assigned consultant at both levels of my relationship. [Account Information] report works fine as each is only counted once. The problem is with the query that combines the 2 tables because the counts show total for each consultant which can be more than one because of locations for each policy number which is the primary key in [Account Information].

Is there a way to restrict the count in the reports to not count duplicates?

--
Brian

Must have been having a bad day. An alias is required on a subquery
in a FROM clause:

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X
GROUP BY [Policy Number]

Are the values given by this query the ones to be put into your
finished product?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom, same error with From clause still.

Maybe I am making this too hard, so I try to explain it better because I think it should be simpler.

I have table [Account Information]which allows no duplicates for "Policy Number" and is joined like you know from code I pasted to [Location] by "Policy Number". This allows there to be many locations for each policy number. The field I am trying to count "Assigned Consultant" is located in the [Location] table so if there are 3 locations for the same policy number the assigned consultant is shown for each record.

What I am trying to do ultimately is in a report being able to show the count for the assigned consulant to be 1 in this case and not 3 as it does currently. I have the report correctly only showing the accounts one time but I need to restrict the count so that I do not get 3 but 1. Is there something simple to do at the report level to restrict what gets counted?

 

[Tom Ellison]

All I can see to do is to work this from the inside out to find out
what's wrong. This is easy enough when I do it with the database
available here, but rougher when we have to converse.

Please try these steps building up this query:

SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]

SELECT *
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X

SELECT *
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X
GROUP BY [Policy Number]

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X
GROUP BY [Policy Number]

Hopefully this would take only a few minutes to test these. I've
included some variants that may be necessary for Jet databases. Is
that what you're using.

I just can't see what is definitely wrong, but if you'll reply with
the results of these it may really help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[bdehning]

SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information] - This one worked and gave me prompr for "Assigned Consultant" and then displayed "Policy Number" Column.

SELECT *
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X - Gave the same syntax error in FROM clause.

SELECT *
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X - Invalid bracketing of name 'Select Distinct [Policy Number'.

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X
GROUP BY [Policy Number] - Syntax error in From clause.

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X
GROUP BY [Policy Number] - Invalid bracketing of name 'Select Distinct [Policy Number'.

Here you go. Thank you for continuing to try and looking at my help it seems I have jet 3.5

I know folks are getting tired of my posts on this subject and I very much appreciate the help and have learned quite a bit already. Please keep trying and I wil explore the SQL some more as well to see what I can figure out now that the first part you sent did work but nothing after.


--
Brian

All I can see to do is to work this from the inside out to find out
what's wrong. This is easy enough when I do it with the database
available here, but rougher when we have to converse.

Please try these steps building up this query:

SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]

SELECT *
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X

SELECT *
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X
GROUP BY [Policy Number]

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X
GROUP BY [Policy Number]

Hopefully this would take only a few minutes to test these. I've
included some variants that may be necessary for Jet databases. Is
that what you're using.

I just can't see what is definitely wrong, but if you'll reply with
the results of these it may really help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

When I try the below SQL I still get syntax eror in FROM clause.

If I can get the count right I use a text box in the report to show the number by using =Sum([CountOfASSIGNED CONSULTANT]) for control source from my original query.

I need reports to count correctly the assigned consultant at both levels of my relationship. [Account Information] report works fine as each is only counted once. The problem is with the query that combines the 2 tables because the counts show total for each consultant which can be more than one because of locations for each policy number which is the primary key in [Account Information].

Is there a way to restrict the count in the reports to not count duplicates?

 

[Tom Ellison]

This helps.

I had understood from your earlier posts that you had a column named
[Assigned Consultant]. Perhaps I'm not typing it exactly correctly.
Please replace that with the correctly typed name of your column and
try these again.

Nobody is forced to read what you and I are exchanging. They do so
only if they are interested. I wouldn't worry about anything but
getting you the help you want. I just hope that works out.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[bdehning]

Tom, Same errors as I posted. I even tried taking out [Assigned Consultant]. Assigned Consultant is correct!

FYI which might help. [Assigned Consultant] is in the "Location" Table of my 1-many relationship from "Account Information" table to "Location" table.

I like you can not figure out the FROM clause issue.

--
Brian

This helps.

I had understood from your earlier posts that you had a column named
[Assigned Consultant]. Perhaps I'm not typing it exactly correctly.
Please replace that with the correctly typed name of your column and
try these again.

Nobody is forced to read what you and I are exchanging. They do so
only if they are interested. I wouldn't worry about anything but
getting you the help you want. I just hope that works out.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information] - This one worked and gave me prompr for "Assigned Consultant" and then displayed "Policy Number" Column.

SELECT *
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X - Gave the same syntax error in FROM clause.

SELECT *
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X - Invalid bracketing of name 'Select Distinct [Policy Number'.

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]) X
GROUP BY [Policy Number] - Syntax error in From clause.

SELECT [Policy Number], COUNT(*) AS CountConsultants
FROM [SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM [Account Information]] X
GROUP BY [Policy Number] - Invalid bracketing of name 'Select Distinct [Policy Number'.

Here you go. Thank you for continuing to try and looking at my help it seems I have jet 3.5

I know folks are getting tired of my posts on this subject and I very much appreciate the help and have learned quite a bit already. Please keep trying and I wil explore the SQL some more as well to see what I can figure out now that the first part you sent did work but nothing after.

 

[Tom Ellison]

With that enlightenment, going back to the overall solution I
proposed:

SELECT DISTINCT A.[Policy Number], A.[Account Name], A.EAP,
A.[Class Code], A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], Sum(A.EAP) AS SumOfEAP, A.[X-Mod],
L.[Assigned Consultant], L.[Location Servicing Division],
(SELECT COUNT(*) FROM (SELECT DISTINCT [Assigned Consultant]
FROM Location L1 WHERE L1.[Policy Number] = A.[Policy Number]))
AS CountConsultants
FROM [Account Information] A
LEFT JOIN Location L ON L.[Policy Number] = A.[Policy Number]
GROUP BY A.[Policy Number], A.[Account Name], A.EAP, A.[Class Code],
A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], A.[X-Mod], L.[Assigned Consultant],
L.[Location Servicing Division];

Now what?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[bdehning]

Still get syntax error in FROM clause.

Anything else I can tell you to help figure this out?
--
Brian

With that enlightenment, going back to the overall solution I
proposed:

SELECT DISTINCT A.[Policy Number], A.[Account Name], A.EAP,
A.[Class Code], A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], Sum(A.EAP) AS SumOfEAP, A.[X-Mod],
L.[Assigned Consultant], L.[Location Servicing Division],
(SELECT COUNT(*) FROM (SELECT DISTINCT [Assigned Consultant]
FROM Location L1 WHERE L1.[Policy Number] = A.[Policy Number]))
AS CountConsultants
FROM [Account Information] A
LEFT JOIN Location L ON L.[Policy Number] = A.[Policy Number]
GROUP BY A.[Policy Number], A.[Account Name], A.EAP, A.[Class Code],
A.[Expiration Date], A.[Service Frequency],
A.[Nature of Operations], A.[X-Mod], L.[Assigned Consultant],
L.[Location Servicing Division];

Now what?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom, Same errors as I posted. I even tried taking out [Assigned Consultant]. Assigned Consultant is correct!

FYI which might help. [Assigned Consultant] is in the "Location" Table of my 1-many relationship from "Account Information" table to "Location" table.

I like you can not figure out the FROM clause issue.

 

[Tom Ellison]

If I could work with it myself a bit, I could probably do it I think.
Want to zip the relevant portion of this and email it to me? This
would probably take much less time. The zipped file must be less than
1 MB or I won't get it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[bdehning]

Sent but not sure you will get. I am cutting down a copy as well just in case to be smaller.

 

[Tom Ellison]

Received

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

This query is working now:

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location) X
GROUP BY [Policy Number]

If you save this query, then join to it on Policy Number, you can
readily retrieve the ConsultantCount values.

Does this get you to the place you wanted to be?

There were some separate issues I asked about earlier, but you seemed
not to want to pursue them. That's fine with me. Let me know if they
become troublesome. I'll keep the data for a while just in case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Douglas J. Steele]

Changes were made to Jet between Access 97 and Access 2000.

In Access 97, the easiest approach is to create 2 queries.

Create a query (call it qryDistinctPolicyConsultant for lack of a better
name) that consists of

SELECT DISTINCT [Policy Number], [Assigned Consultant] FROM Location

Create a 2nd query based on qryDistinctPolicyConsultant

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM qryDistinctPolicyConsultant
GROUP BY [Policy Number]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If someone sees this post can they explain why the query you posted works

in Access 2000 but not Access 97 which I need to run to cover all of our
offices.

The issue for mosts of the prior posts must be Office 97 related.
--
Brian

This query is working now:

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location) X
GROUP BY [Policy Number]

If you save this query, then join to it on Policy Number, you can
readily retrieve the ConsultantCount values.

Does this get you to the place you wanted to be?

There were some separate issues I asked about earlier, but you seemed
not to want to pursue them. That's fine with me. Let me know if they
become troublesome. I'll keep the data for a while just in case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Sent but not sure you will get. I am cutting down a copy as well just

in case to be smaller.

 

[Gary Walter]

Hi Brian,

PMFBI

If you copy query to Access 97
and look at how the SQL
is changed, it will probably
look like:

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM
[SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location]. X
GROUP BY [Policy Number]

The brackets and the ending period were the only
way to use a subquery in the FROM clause in 97.

PLUS...
you could *not* use any brackets within the subquery....

In 97, you will need to save the subquery as a
named stored query (say "qryDistPolNum"):

SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location;

then use the stored query in the FROM clause
in place of the subquery...

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM
qryDistPolNum
GROUP BY [Policy Number]

Please respond back if I have not been clear
about something....

and apologies for butting in...

Good luck,

Gary Walter

"bdehning"

If someone sees this post can they explain why the query you posted works in Access

2000 but not Access 97 which I need to run to cover all of our offices.

The issue for mosts of the prior posts must be Office 97 related.
--
Brian

This query is working now:

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location) X
GROUP BY [Policy Number]

If you save this query, then join to it on Policy Number, you can
readily retrieve the ConsultantCount values.

Does this get you to the place you wanted to be?

There were some separate issues I asked about earlier, but you seemed
not to want to pursue them. That's fine with me. Let me know if they
become troublesome. I'll keep the data for a while just in case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Sent but not sure you will get. I am cutting down a copy as well just in case

to be smaller.

 

[Gary Walter]

Hi Brian,

PMFBI

If you copy query to Access 97
and look at how the SQL
is changed, it will probably
look like:

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM
[SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location]. X
GROUP BY [Policy Number]

The brackets and the ending period were the only
way to use a subquery in the FROM clause in 97.

PLUS...
you could *not* use any brackets within the subquery....

In 97, you will need to save the subquery as a
named stored query (say "qryDistPolNum"):

SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location;

then use the stored query in the FROM clause
in place of the subquery...

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM
qryDistPolNum
GROUP BY [Policy Number];

Or.....
change your field names so you there are no spaces
in the name, so you won't have to wrap them in brackets.

Please respond back if I have not been clear
about something....

and apologies for butting in...

Good luck,

Gary Walter

"bdehning"

If someone sees this post can they explain why the query you posted works in Access

2000 but not Access 97 which I need to run to cover all of our offices.

The issue for mosts of the prior posts must be Office 97 related.
--
Brian

This query is working now:

SELECT [Policy Number],
COUNT([Assigned Consultant]) AS ConsultantCount
FROM (SELECT DISTINCT [Policy Number], [Assigned Consultant]
FROM Location) X
GROUP BY [Policy Number]

If you save this query, then join to it on Policy Number, you can
readily retrieve the ConsultantCount values.

Does this get you to the place you wanted to be?

There were some separate issues I asked about earlier, but you seemed
not to want to pursue them. That's fine with me. Let me know if they
become troublesome. I'll keep the data for a while just in case.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Sent but not sure you will get. I am cutting down a copy as well just in case

to be smaller.