Exclude Duplicates in Report Counts

B

bdehning

I have been struggling for days to try and figure out how to get correct counts in my report. I have a table [Account Information] that has primary key Policy number which is joined 1-many to a [Location] table. I have "Controlling Consultant" in the table [Account Information] and "Assigned Consultant" in the [Location] Table. They is only one policy number for each account in account information but there can be many locations for each policy number in [Location].

My joined query produces a correct report with accurate counts for "Controlling consultant". The query I produce for assigned consultant produces counts for each entry is there are more than 1 location. When I do a count of "assigned Consultant" I get numbers reflecting the number of locations.

Barry - 3
Don -4
Chuck -5

I would like to be able to count such that each person at the [location] level is counted just once so it would show

Barry -1
Don -1
Chuck -1

Here is the SQL that gives me the wrong totals

SELECT DISTINCT Location.[Assigned Consultant], [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], Count(Location.[Assigned Consultant]) AS [CountOfAssigned Consultant], Location.[Location Servicing Division]
FROM [Account Information] LEFT JOIN Location ON [Account Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], [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.[Location Servicing Division]
HAVING (((Location.[Assigned Consultant]) Like [Enter Assigned Consultant] & "*"));

Can any one help me figure this out? I have tried to work with the SQL but keep getting From clause problems and have tried reading other postes with no success so far.

Is there away to exclude the duplicates in the report by chance?
Brian
 
K

Ken Snell

bdehning -

This is the third post in three different newsgroups in past three days.
It's much more effective for you and responders if you stick to a single
thread/post so that all can see everything that was already discussed and
tried.
 
D

Duane Hookom

First, I would change the parameter query for a reference to a control on a
form. You could then get unique consultant by location with:
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";
Then use this query to calculate the counts as you need them. Add the final
query to your first query and join the location fields to get your counts
for each. I can only assume this is what you want.

--
Duane Hookom
MS Access MVP


bdehning said:
I have been struggling for days to try and figure out how to get correct
Click to expand...
counts in my report. I have a table [Account Information] that has primary
key Policy number which is joined 1-many to a [Location] table. I have
"Controlling Consultant" in the table [Account Information] and "Assigned
Consultant" in the [Location] Table. They is only one policy number for
each account in account information but there can be many locations for each
policy number in [Location].
My joined query produces a correct report with accurate counts for
Click to expand...
"Controlling consultant". The query I produce for assigned consultant
produces counts for each entry is there are more than 1 location. When I do
a count of "assigned Consultant" I get numbers reflecting the number of
locations.
Barry - 3
Don -4
Chuck -5

I would like to be able to count such that each person at the [location]
Click to expand...
level is counted just once so it would show
Barry -1
Don -1
Chuck -1

Here is the SQL that gives me the wrong totals

SELECT DISTINCT Location.[Assigned Consultant], [Account
Click to expand...
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], Count(Location.[Assigned
Consultant]) AS [CountOfAssigned Consultant], Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Click to expand...
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], [Account Information].[Policy
Click to expand...
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.[Location Servicing
Division]
HAVING (((Location.[Assigned Consultant]) Like [Enter Assigned Consultant] & "*"));

Can any one help me figure this out? I have tried to work with the SQL but
Click to expand...
keep getting From clause problems and have tried reading other postes with
no success so far.
 
B

bdehning

Duane, thanks for trying to help.

I get a syntax error in join operation trying your SQL.
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";


I was not sure how to add the query if I get an error above?
--
Brian


Duane Hookom said:
First, I would change the parameter query for a reference to a control on a
form. You could then get unique consultant by location with:
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";
Then use this query to calculate the counts as you need them. Add the final
query to your first query and join the location fields to get your counts
for each. I can only assume this is what you want.

--
Duane Hookom
MS Access MVP


bdehning said:
I have been struggling for days to try and figure out how to get correct
Click to expand...
counts in my report. I have a table [Account Information] that has primary
key Policy number which is joined 1-many to a [Location] table. I have
"Controlling Consultant" in the table [Account Information] and "Assigned
Consultant" in the [Location] Table. They is only one policy number for
each account in account information but there can be many locations for each
policy number in [Location].
My joined query produces a correct report with accurate counts for
Click to expand...
"Controlling consultant". The query I produce for assigned consultant
produces counts for each entry is there are more than 1 location. When I do
a count of "assigned Consultant" I get numbers reflecting the number of
locations.
Barry - 3
Don -4
Chuck -5

I would like to be able to count such that each person at the [location]
Click to expand...
level is counted just once so it would show
Barry -1
Don -1
Chuck -1

Here is the SQL that gives me the wrong totals

SELECT DISTINCT Location.[Assigned Consultant], [Account
Click to expand...
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], Count(Location.[Assigned
Consultant]) AS [CountOfAssigned Consultant], Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Click to expand...
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], [Account Information].[Policy
Click to expand...
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.[Location Servicing
Division]
HAVING (((Location.[Assigned Consultant]) Like [Enter Assigned Consultant] & "*"));

Can any one help me figure this out? I have tried to work with the SQL but
Click to expand...
keep getting From clause problems and have tried reading other postes with
no success so far.
Is there away to exclude the duplicates in the report by chance?
Brian
Click to expand...
Click to expand...
 
D

Duane Hookom

Fix the join. I think "LEFT" should be replaced by "INNER". Just create the
query but then go back to your other thread if this is being discussed
elsewhere.

--
Duane Hookom
MS Access MVP


bdehning said:
Duane, thanks for trying to help.

I get a syntax error in join operation trying your SQL.
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";


I was not sure how to add the query if I get an error above?
--
Brian


Duane Hookom said:
First, I would change the parameter query for a reference to a control on a
form. You could then get unique consultant by location with:
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";
Then use this query to calculate the counts as you need them. Add the final
query to your first query and join the location fields to get your counts
for each. I can only assume this is what you want.

--
Duane Hookom
MS Access MVP


bdehning said:
I have been struggling for days to try and figure out how to get
Click to expand...
correct
counts in my report. I have a table [Account Information] that has primary
key Policy number which is joined 1-many to a [Location] table. I have
"Controlling Consultant" in the table [Account Information] and "Assigned
Consultant" in the [Location] Table. They is only one policy number for
each account in account information but there can be many locations for each
policy number in [Location].
My joined query produces a correct report with accurate counts for
Click to expand...
"Controlling consultant". The query I produce for assigned consultant
produces counts for each entry is there are more than 1 location. When I do
a count of "assigned Consultant" I get numbers reflecting the number of
locations.
Barry - 3
Don -4
Chuck -5

I would like to be able to count such that each person at the
Click to expand...
[location]
level is counted just once so it would show
Barry -1
Don -1
Chuck -1

Here is the SQL that gives me the wrong totals

SELECT DISTINCT Location.[Assigned Consultant], [Account
Click to expand...
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], Count(Location.[Assigned
Consultant]) AS [CountOfAssigned Consultant], Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Click to expand...
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], [Account Information].[Policy
Click to expand...
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.[Location Servicing
Division]
HAVING (((Location.[Assigned Consultant]) Like [Enter Assigned
Click to expand...
Consultant]
& "*"));
Can any one help me figure this out? I have tried to work with the SQL
Click to expand...
but
keep getting From clause problems and have tried reading other postes with
no success so far.
Is there away to exclude the duplicates in the report by chance?
Brian
Click to expand...
Click to expand...
Click to expand...
 
B

bdehning

Duane,

I tried inner and no luck.

Thanks for trying. I will continue with my thread with Tom Ellison in hopes of resolving this issue
--
Brian


Duane Hookom said:
Fix the join. I think "LEFT" should be replaced by "INNER". Just create the
query but then go back to your other thread if this is being discussed
elsewhere.

--
Duane Hookom
MS Access MVP


bdehning said:
Duane, thanks for trying to help.

I get a syntax error in join operation trying your SQL.
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";


I was not sure how to add the query if I get an error above?
--
Brian


Duane Hookom said:
First, I would change the parameter query for a reference to a control on a
form. You could then get unique consultant by location with:
SELECT Location.[Assigned Consultant] , Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], Location.[Location Servicing
Division]
HAVING Location.[Assigned Consultant] Like Forms!frmA!txtConsultant & "*";
Then use this query to calculate the counts as you need them. Add the final
query to your first query and join the location fields to get your counts
for each. I can only assume this is what you want.

--
Duane Hookom
MS Access MVP


I have been struggling for days to try and figure out how to get correct
counts in my report. I have a table [Account Information] that has primary
key Policy number which is joined 1-many to a [Location] table. I have
"Controlling Consultant" in the table [Account Information] and "Assigned
Consultant" in the [Location] Table. They is only one policy number for
each account in account information but there can be many locations for each
policy number in [Location].

My joined query produces a correct report with accurate counts for
"Controlling consultant". The query I produce for assigned consultant
produces counts for each entry is there are more than 1 location. When I do
a count of "assigned Consultant" I get numbers reflecting the number of
locations.

Barry - 3
Don -4
Chuck -5

I would like to be able to count such that each person at the [location]
level is counted just once so it would show

Barry -1
Don -1
Chuck -1

Here is the SQL that gives me the wrong totals

SELECT DISTINCT Location.[Assigned Consultant], [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], Count(Location.[Assigned
Consultant]) AS [CountOfAssigned Consultant], Location.[Location Servicing
Division]
FROM [Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]
GROUP BY Location.[Assigned Consultant], [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.[Location Servicing
Division]
HAVING (((Location.[Assigned Consultant]) Like [Enter Assigned Consultant]
& "*"));

Can any one help me figure this out? I have tried to work with the SQL but
keep getting From clause problems and have tried reading other postes with
no success so far.

Is there away to exclude the duplicates in the report by chance?
Brian
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

Do not Count Duplicates 23
Count Function (conditional counts) in Reports 3
Partial Name Prompt Question 3
Union Query Problems 0
Criteria to open form 1
Duplicating Main Form And Its Subform Detail Records in Access 97 3
Counting within Groups within a Report 4
Memo Field Truncated in Access 97. 0

Top