Solved

Differing values for the same policy numbers.

Posted on 2014-12-17
6
127 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 36

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

 
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 36

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

830 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