Solved

Differing values for the same policy numbers.

Posted on 2014-12-17
6
126 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 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 35

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 35

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

770 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