Differing values for the same policy numbers.

Hello expert,
Have a table with field 'Policy_numbers' There are many 'Policy_numbers' that are repeated multiple times.
There is also a row called 'Commission_rate'. Most of the fields that have the same Policy_number have the
same value for Commission_rate. But would like to test to see any of the rows that have the same Policy_number have more than one Commission_rate.

I think that the comparison could be done with Group by or Having Query but not sure about the syntax.

Thanks.

Allen in Dallas
LVL 1
Allen PittsBusiness analystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this query

select Policy_numbers, Commission_rate
from TableName
group by  Policy_numbers, Commission_rate

or

select Policy_numbers, Commission_rate
from TableName
where  Commission_rate= <some value>  
group by  Policy_numbers


change  <some value>   with any values listed in Commission_rate
0
PatHartmanCommented:
Sounds like a poor table design that you might want to fix.

In any event, use the query wizard to build the query.  A simplistic solution is to use nested queries
qry1
Select Distinct Policy_Number, Commission_Rate From YourTable;

qry2
Select Policy_Number, Count(*) as RecCount From qry1
Group by Policy_Number
Having Count(*) > 1;

SQL Server has the ability to combine into one query but Access needs two or one with a subquery.  The first query gets the distinct values of the two fields, the second groups on the first field and counts the instances caused by variations in the second field.
0
Nick67Commented:
Seeing this, after I posted it, it's saying the same as @PatHartman

I don't know, to me
But would like to test to see any of the rows that have the same Policy_number have more than one Commission_rate.


Sounds like a query with
"Select Distinct Policy_number, Commission_rate from WhateverTheTableIs Order by Policy_number;"
Run the query
Scan downward.
Any Policy number that shows up more than once, shows up because it has more than one commission rate and you can view them.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HainKurtSr. System AnalystCommented:
here it is:

with t as (
select 1 Policy_numbers, 1 Commission_rate, 'a1' info
union select 1, 1, 'a2'
union select 1, 2, 'a3'
union select 2, 2, 'b1'
union select 2, 2, 'b2'
union select 3, 1, 'c1'
union select 3, 2, 'c2'
union select 3, 1, 'c3'
)
select Policy_numbers, count (distinct Commission_rate) times from t
group by Policy_numbers
having count (distinct Commission_rate) > 1

Policy_numbers	times
1	2
3	2

Open in new window


it shows, Policy_numbers=1,3 have more than 1 distinct Commission_rate, where Policy_numbers=2 is ok, all same... not included in result...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
HainKurt  - that is not standard Access SQL.  That is the T-SQL syntax I was referring to that allows the query to be done in one step.

@9apit - Are you using pass-through queries to SQL?  Is that why you accepted this answer?
0
Allen PittsBusiness analystAuthor Commented:
Hello PatHartman,

I was able to take the T-SQL and adapt it to Access syntax

Allen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.