?
Solved

Differing values for the same policy numbers.

Posted on 2014-12-17
6
Medium Priority
?
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 38

Assisted Solution

by:PatHartman
PatHartman earned 668 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 668 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 57

Accepted Solution

by:
HainKurt earned 664 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 38

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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