Solved

Differing values for the same policy numbers.

Posted on 2014-12-17
6
124 Views
Last Modified: 2014-12-17
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
0
Comment
Question by:9apit
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40505470
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 167 total points
ID: 40505490
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 167 total points
ID: 40505516
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Accepted Solution

by:
HainKurt earned 166 total points
ID: 40505549
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40505586
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
 

Author Comment

by:9apit
ID: 40505675
Hello PatHartman,

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

Allen
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now