Query Counting Question

[bdehning]

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned Consultant" Names. What do I need to fix in the above Statement to count each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[Ken Snell]

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned

Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

 

[bdehning]

Ken,

I tried that and got The syntax of the subquery in this expression is incorrect. "Check the subquery's syntax and enclose the subquery in parentheses.

Just to verify. I am using the expression in the criteria of the field "Assigned Consultant" with Table "Location" and Total "Count". I also have a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned

Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[Ken Snell]

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I tried that and got The syntax of the subquery in this expression is

incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the field

"Assigned Consultant" with Table "Location" and Total "Count". I also have
a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned

Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[bdehning]

Ken thanks for the reply. I am the confused one since like I said I was not sure where to put the expression.

This is the SQL from the Query where I want to do the counting. I think your saying this is where the expression should go. I thought I could useu query design and the fields to place the expression you corrected for me.

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], Count(Location.[Assigned Consultant]) AS [CountOfAssigned Consultant], 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];

Can you work this this and place the expression?

Thanks

--
Brian

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I tried that and got The syntax of the subquery in this expression is

incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the field

"Assigned Consultant" with Table "Location" and Total "Count". I also have
a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned
Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[Ken Snell]

DISTINCT is redundant in a Totals query. You have a Totals query here. So
that is not needed.

The rest of the SQL looks ok to me. I can't tell because of newsreader
wrapping, but make sure that you do not have any carriage returns in the SQL
string (the SQL statement is breaking between table name and field name, for
example, in the post). The presence of a carriage return in places where the
SQL expects to have continuous text can cause problems with
misinterpretation. An ending . followed by space can be interpreted by Jet
as the end of a subquery.

For example, don't do this by using a carriage return:
Location.
[Policy Number]


--

Ken Snell
<MS ACCESS MVP>

Ken thanks for the reply. I am the confused one since like I said I was

not sure where to put the expression.

This is the SQL from the Query where I want to do the counting. I think

your saying this is where the expression should go. I thought I could useu
query design and the fields to place the expression you corrected for me.

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], Count(Location.[Assigned Consultant]) AS
[CountOfAssigned Consultant], 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];

Can you work this this and place the expression?

Thanks

--
Brian

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I tried that and got The syntax of the subquery in this expression is

incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the

field
"Assigned Consultant" with Table "Location" and Total "Count". I also have
a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian


:

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned
Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[bdehning]

Ken I still need your help in placing the expression you provided me!!!!!

This is the query without the field for count of Assigned consultant that was giving me the duplicating count problem. I assume I need to insert what you sent me.

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

Thanks
--


Brian

DISTINCT is redundant in a Totals query. You have a Totals query here. So
that is not needed.

The rest of the SQL looks ok to me. I can't tell because of newsreader
wrapping, but make sure that you do not have any carriage returns in the SQL
string (the SQL statement is breaking between table name and field name, for
example, in the post). The presence of a carriage return in places where the
SQL expects to have continuous text can cause problems with
misinterpretation. An ending . followed by space can be interpreted by Jet
as the end of a subquery.

For example, don't do this by using a carriage return:
Location.
[Policy Number]


--

Ken Snell
<MS ACCESS MVP>

Ken thanks for the reply. I am the confused one since like I said I was

not sure where to put the expression.

This is the SQL from the Query where I want to do the counting. I think

your saying this is where the expression should go. I thought I could useu
query design and the fields to place the expression you corrected for me.

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], Count(Location.[Assigned Consultant]) AS
[CountOfAssigned Consultant], 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];

Can you work this this and place the expression?

Thanks

--
Brian

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>


Ken,

I tried that and got The syntax of the subquery in this expression is
incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the field
"Assigned Consultant" with Table "Location" and Total "Count". I also have
a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian


:

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned
Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[Ken Snell]

Sorry - I was focused on your syntax error problem and not a duplicative
counting problem.

However, I note that you've started a new thread on this same topic in the
queries newsgroup, and there is no need for maintaining two separate threads
on the exact same topic.

As you've started a new thread, let's close this one and let that one
continue. OK?

--

Ken Snell
<MS ACCESS MVP>

Ken I still need your help in placing the expression you provided me!!!!!

This is the query without the field for count of Assigned consultant that

was giving me the duplicating count problem. I assume I need to insert what
you sent me.

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

Thanks
--


Brian

DISTINCT is redundant in a Totals query. You have a Totals query here. So
that is not needed.

The rest of the SQL looks ok to me. I can't tell because of newsreader
wrapping, but make sure that you do not have any carriage returns in the SQL
string (the SQL statement is breaking between table name and field name, for
example, in the post). The presence of a carriage return in places where the
SQL expects to have continuous text can cause problems with
misinterpretation. An ending . followed by space can be interpreted by Jet
as the end of a subquery.

For example, don't do this by using a carriage return:
Location.
[Policy Number]


--

Ken Snell
<MS ACCESS MVP>

Ken thanks for the reply. I am the confused one since like I said I

was
not sure where to put the expression.

This is the SQL from the Query where I want to do the counting. I

think
your saying this is where the expression should go. I thought I could useu
query design and the fields to place the expression you corrected for me.

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], Count(Location.[Assigned Consultant]) AS
[CountOfAssigned Consultant], 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];

Can you work this this and place the expression?

Thanks

--
Brian


:

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>


Ken,

I tried that and got The syntax of the subquery in this expression is
incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the field
"Assigned Consultant" with Table "Location" and Total "Count". I

also
have

a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian


:

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned
Consultant" Names. What do I need to fix in the above Statement

to
count

each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[ËïÉÙ¾ü]

DISTINCT is redundant in a Totals query. You have a Totals query here. So
that is not needed.

The rest of the SQL looks ok to me. I can't tell because of newsreader
wrapping, but make sure that you do not have any carriage returns in the SQL
string (the SQL statement is breaking between table name and field name, for
example, in the post). The presence of a carriage return in places where the
SQL expects to have continuous text can cause problems with
misinterpretation. An ending . followed by space can be interpreted by Jet
as the end of a subquery.

For example, don't do this by using a carriage return:
Location.
[Policy Number]


--

Ken Snell
<MS ACCESS MVP>

Ken thanks for the reply. I am the confused one since like I said I was

not sure where to put the expression.

This is the SQL from the Query where I want to do the counting. I think

your saying this is where the expression should go. I thought I could useu
query design and the fields to place the expression you corrected for me.

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], Count(Location.[Assigned Consultant]) AS
[CountOfAssigned Consultant], 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];

Can you work this this and place the expression?

Thanks

--
Brian

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>


Ken,

I tried that and got The syntax of the subquery in this expression is
incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the field
"Assigned Consultant" with Table "Location" and Total "Count". I also have
a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian


:

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned
Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB

 

[bdehning]

I need help placing the SQL within my already SQL. I keep getting syntax error in FROM clause when I try placing the SQL. My SQL works OK its ther additional one I have trouble with.

Any ideas?
--
Brian

DISTINCT is redundant in a Totals query. You have a Totals query here. So
that is not needed.

The rest of the SQL looks ok to me. I can't tell because of newsreader
wrapping, but make sure that you do not have any carriage returns in the SQL
string (the SQL statement is breaking between table name and field name, for
example, in the post). The presence of a carriage return in places where the
SQL expects to have continuous text can cause problems with
misinterpretation. An ending . followed by space can be interpreted by Jet
as the end of a subquery.

For example, don't do this by using a carriage return:
Location.
[Policy Number]


--

Ken Snell
<MS ACCESS MVP>

Ken thanks for the reply. I am the confused one since like I said I was

not sure where to put the expression.

This is the SQL from the Query where I want to do the counting. I think

your saying this is where the expression should go. I thought I could useu
query design and the fields to place the expression you corrected for me.

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], Count(Location.[Assigned Consultant]) AS
[CountOfAssigned Consultant], 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];

Can you work this this and place the expression?

Thanks

--
Brian


:

Sorry - try this:

SELECT Count(*) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])


But I am confused...you state that you're using the expression in the
criteria of the field "Assigned Consultant". What expression? Post the
entire SQL that you're trying to run, please.
--

Ken Snell
<MS ACCESS MVP>


Ken,

I tried that and got The syntax of the subquery in this expression is
incorrect. "Check the subquery's syntax and enclose the subquery in
parentheses.

Just to verify. I am using the expression in the criteria of the field
"Assigned Consultant" with Table "Location" and Total "Count". I also have
a field "Assigned Consultant" to Group By in this query.

Do I have the expression in the right spot?
--
Brian


:

You need ( ) around the field that you're counting.

SELECT Count([Assigned Consultant]) FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

--

Ken Snell
<MS ACCESS MVP>

SELECT Count[Assigned Consultant] FROM (SELECT DISTINCT [Assigned
Consultant] FROM [Location])

I have syntax problem with above statement.

I have a subform "location" table which gives me duplicate "Assigned
Consultant" Names. What do I need to fix in the above Statement to count
each individual Assigned Consultant only once in a query.

For Example, Count below should be 3 not 5.

SAM
SAM
RICK
RICK
BOB