Do not Count Duplicates

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

 

[Gary Walter]

Boy..when that Walter guy butts in,
he really butts in...

sorry for multple posts..

plus now I see already answered anyway...

any other questions Brian?

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


On Sat, 24 Jul 2004 15:37:02 -0700, "bdehning"

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

 

[Tom Ellison]

Gary,

Please to butt in whenever you wish. You always contribute
positively.

I tries something like this at one point but it didn't seem to help.
But like you said, Access 97 will usually add this automatically, so
it wouldn't be the problem (I thought).

Your suggestion on how to break it up to make it work is simple and
direct.

You know, I used to work in Access 97 queries all the time. But I
haven't now for 4 years. There were so many things that didn't work,
or that required work-arounds.

Can users upgrade the Jet they use with A97 to overcome this? I
switched to using MSDE or SQL Server exclusively (as I know you are
already aware) and I just haven't kept up with this end.

So, I really did want others to come into the discussion. It had left
the realm of what I felt confident to answer.

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

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


On Sat, 24 Jul 2004 15:37:02 -0700, "bdehning"

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

 

[Guest]

Thanks to all who helped on this post and especially Tom and Gary. Took allot of time and effort by Tom.

Thanks again Tom and Gary.
--
Brian

Boy..when that Walter guy butts in,
he really butts in...

sorry for multple posts..

plus now I see already answered anyway...

any other questions Brian?

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


On Sat, 24 Jul 2004 15:37:02 -0700, "bdehning"

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