Query to get only one record per Client. (1 Viewer)

pr2-eugin

Super Moderator
Local time
Today, 19:03
Joined
Nov 30, 2011
Messages
8,494
I am working on an Insurance DB.. I have two tables tbl_Clients and tbl_PolicyDetails with a One-Many relationship.. tbl_Clients have the Clients basic info, Name, Tel, Address etc. The tbl_PolicyDetails have the details of the policy for the customer like Start date, policy term, amount they pay, policy status etc.

Policy Status can be any of the following Quote, Active, Suspended, Lapsed, Cancelled, Terminated..

One client can have many Policies.. When one policy finishes (like when cover upgraded/downgraded/any changed their insured property address etc.) we categorise them as Termination of old policy thus the client will have a New policy.. At any stage we only need the newest policy per client.. That can have any of the status as mentioned above..

My problem now is I need to list all Clients with policy status Active, Suspended, Lapsed, Cancelled..
Code:
SELECT tbl_Clients.cli_ID, tbl_Clients.firstName, tbl_Clients.address, tbl_PolicyDetails.policyCover 
FROM tbl_Clients INNER JOIN tbl_PolicyDetails ON tbl_Clients.cli_ID = tbl_PolicyDetails.cliID_FK
WHERE tbl_PolicyDetails.policyStatus = "Active" Or tbl_PolicyDetails.policyStatus = "Suspended" Or tbl_PolicyDetails.policyStatus = "Lapsed" Or tbl_PolicyDetails.policyStatus = "Cancelled";
This would work in an ideal environment.. However, Consider the following data..
tbl_Clients
Code:
cli_ID   firstName      address
1        Paul        Bournemouth
2        Mark        Bristol
3        Lauren      London
4        Caryn       Devon
tbl_PolicyDetails
Code:
pol_ID   cliID_FK  policyCover    policyStatus
1        1         PDB            Active
2        2         PD             Terminated    
3        2         PDB            Active
4        3         PDB            Cancelled
5        4         PDR            Lapsed
6        4         PDE            Active
7        3         PDBCH          Suspended
When a Client wishes to Cancel a policy it is not Terminated, the policy status is just Cancelled.. Similar to Lapsed policy, if the policy hits 30 days of no pay then it is passed on to a Debt collection agency.. If in future the Client comes back a new policy is generated for both cases.. So in this case Client Lauren and Caryn will have been repeated twice, in the report I am trying to generate (based on the above query).. I do not wish to display the Cancelled policy if the customer has another policy which again can be Active, Suspended, Lapsed, Cancelled.. So the result I wish to have for the above data set would be..
Code:
cli_ID   firstName    address      policyCover    policyStatus
1        Paul        Bournemouth     PDB        Active
2        Mark        Bristol         PDB        Active
3        Lauren      London          PDBCH      Suspended
4        Caryn       Devon           PDE        Active
Would appreciate some insight.. I am not sure how to apply this rule (in plain words)
If a client has more than one policy, take only the last policy into the Query..
I have attached the Dummy version of the DB file..
 

Attachments

  • SampleDB - Copy.mdb
    364 KB · Views: 124

Mr. B

"Doctor Access"
Local time
Today, 14:03
Joined
May 20, 2009
Messages
1,932
I would suggest that you add a Date/Time field to the "tbl_PolicyDetails" table and have this date field to be updated anytime the PolicyStatus field changes. You would then be able to retrieve the most recently changed or modified record.

You might also want to consider creating a table of your Status values and then use a number type field as a foreign key field in the "tbl_PolicyDetails" to link to the new table of Status values rather than use Text values for the PolicyStatus field.
 

pr2-eugin

Super Moderator
Local time
Today, 19:03
Joined
Nov 30, 2011
Messages
8,494
I should have mentioned there is a field in the Table tbl_PolicyDetails called policyStarted which will have the Start date of the Policy.. So when a Policy is Terminated and a New policy is set up for the Client; a New start date is generated..

So in other words.. I would want only the Max(policyStarted) to be returned for each Client..
 

pr2-eugin

Super Moderator
Local time
Today, 19:03
Joined
Nov 30, 2011
Messages
8,494
Okay the following Query seems to get what I want..
Code:
SELECT tbl_Clients.cliID, tbl_Clients.firstName, tbl_Clients.address, tbl_PolicyDetails.policyCover, tbl_PolicyDetails.policyStatus
FROM tbl_Clients INNER JOIN (tbl_PolicyDetails INNER JOIN (SELECT tbl_PolicyDetails.cliID_FK, Max(tbl_PolicyDetails.policyStarted) AS MaxOfpolicyStarted
FROM tbl_PolicyDetails
GROUP BY tbl_PolicyDetails.cliID_FK)  AS testSub ON (tbl_PolicyDetails.cliID_FK = testSub.cliID_FK) AND (tbl_PolicyDetails.policyStarted = testSub.MaxOfpolicyStarted)) ON tbl_Clients.cliID = tbl_PolicyDetails.cliID_FK
WHERE (((tbl_PolicyDetails.policyStatus)="Active" Or (tbl_PolicyDetails.policyStatus)="Suspended" Or (tbl_PolicyDetails.policyStatus)="Lapsed" Or (tbl_PolicyDetails.policyStatus)="Cancelled"))
ORDER BY tbl_Clients.cliID;
Used a Subquery to get the Max(policyStarted).. Then performed a join with the tbl_PolicyDetails.. Is there any possible problem that I am not looking at?
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Jan 23, 2006
Messages
15,364
Here's another
Code:
SELECT tbl_Clients.cliID
, tbl_Clients.firstName
, tbl_Clients.address
, tbl_PolicyDetails.policyCover
, tbl_PolicyDetails.policyStatus
FROM 
tbl_Clients INNER JOIN tbl_PolicyDetails ON
                                    tbl_Clients.cliID = tbl_PolicyDetails.cliID_FK
WHERE 
  tbl_PolicyDetails.policyStatus In("Active","Suspended","Lapsed","Cancelled") and
  tbl_PolicyDetails.PolID In 
                                          (SELECT TOP 1 POLId from tbl_PolicyDetails as DDD
                                            where DDD.cliID_FK = tbl_PolicyDetails.cliID_FK
                                            Order By PolID DESC )
 ORDER BY tbl_Clients.cliID;
 

pr2-eugin

Super Moderator
Local time
Today, 19:03
Joined
Nov 30, 2011
Messages
8,494
Neato.. Thanks jdraw.. It looks good.. and returns the result I want, without complicating too many Joins.. I just thought TOP and ORDER BY never worked together..

In some Queries I have used in the past; if I use TOP along with ORDER BY the result always was not correct.. If I use TOP 10 and ORDER BY the Order by clause is applied but TOP would not be effective.. In this case it works.. :) Am happy..

Would be glad if you could shed some light on this.. Again Thank you..
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Jan 23, 2006
Messages
15,364
Paul,

I played with the sql later based on your comments. I chose Order By...DESC
but I think the proper sql is as follows with the Status included in the subquery.

Code:
SELECT tbl_Clients.cliID
, tbl_Clients.firstName
, tbl_Clients.address
, tbl_PolicyDetails.policyCover
, tbl_PolicyDetails.policyStatus
FROM 
tbl_Clients INNER JOIN tbl_PolicyDetails ON
                                    tbl_Clients.cliID = tbl_PolicyDetails.cliID_FK
WHERE 
  
  tbl_PolicyDetails.PolID In 
                             (SELECT TOP 1 POLId from tbl_PolicyDetails as DDD
                              where DDD.cliID_FK = tbl_PolicyDetails.cliID_FK
                              and 
                              DDD.policyStatus In("Active","Suspended","Lapsed","Cancelled") 
                              Order By PolID  )
 ORDER BY tbl_Clients.cliID;

And you can use either ASC or DESC for the Order By in the subquery.
 

Brianwarnock

Retired
Local time
Today, 19:03
Joined
Jun 2, 2003
Messages
12,701
I never use sub queries when a simple cascade will do, what you are asking for is actually very simple and standard practice.

First query finds max in group, second joins this info back to table to extract the rest of the info.

Just my 2 penny worth

Brian
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Jan 23, 2006
Messages
15,364
I agree generally Brian. But what do you do when you want the Top 3 of something in a group..

eg.
SELECT t1.shortname, t1.GL, t1.ProductName, t1.totalEndingInventory, t1.Jid
FROM WeeklyEndingInventory_New AS t1
WHERE (((t1.Jid) In (SELECT TOP 3 T2.JId FROM WeeklyEndingInventory_new As T2
WHERE T2.shortname = T1.shortname ORDER BY T2.JID asC)))
GROUP BY t1.gl, t1.shortname, t1.productname, t1.totalendinginventory, t1.jid
ORDER BY t1.shortname, t1.totalEndingInventory DESC;
 

Brianwarnock

Retired
Local time
Today, 19:03
Joined
Jun 2, 2003
Messages
12,701
I agree generally Brian. But what do you do when you want the Top 3 of something in a group..

:confused:
That was not the question, or did I miss something .
If the problem was my statement about sub queries then note what I actually said, I do use subqueries when I have to, I have not found a way of doing ranking without using a subquery for example.

This should all be in the past tense of course as I only use access on here for the past 7 years.

Brian
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Jan 23, 2006
Messages
15,364
Brian,
I was just saying there is more than 1 way to skin a cat. I wasn't trying to dissuade/persuade anything or anybody. I gave a sample(my original post) and I subsequently saw an error and corrected it.
I certainly agree, and normally would use a two query solution (cascade) for the problem as stated (Max).

My comment was just pointing out that a simple cascade has its place, as do subqueries.

You are correct a simple Max and second query would suffice.
 
Last edited:

Brianwarnock

Retired
Local time
Today, 19:03
Joined
Jun 2, 2003
Messages
12,701
J
I agree with all that you say, except that I didn't spot the error. :D

Brian
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Jan 23, 2006
Messages
15,364
Brian,
if you use my original code, and use Order by Asc - there is an error.
The record selected for Mark has status "Terminated" and is discarded.
 

pr2-eugin

Super Moderator
Local time
Today, 19:03
Joined
Nov 30, 2011
Messages
8,494
Brian and jdraw, Thank you guys for your valuable comments.. I finally decided to use two Queries (based on what I have in Post #4), Created a Query to get the Max() and the used it to Join on the Query that normally Joined Client with their Policies..

JDraw, as for your code, it works great.. I am just trying to see in this scenario which will be efficient.. Using a Saved(pre compiled) Query or SubQuery..

But I am not entirely certain SubQuery would be faster, so ended up using two Queries..

Thank you.. :)
 

Brianwarnock

Retired
Local time
Today, 19:03
Joined
Jun 2, 2003
Messages
12,701
Paul FWIW a discussion took place on this forum a few years ago involving a few of the "senior" members including a couple of MVPs on sub versus cascading query performance, I can't find it these days but I'm sure the outcome was that normally there would be nothing in it , however the sub would never be faster than the cascading and could be slower as your code might compromise the optimisation of the compiler.

Brian
 

pr2-eugin

Super Moderator
Local time
Today, 19:03
Joined
Nov 30, 2011
Messages
8,494
....however the sub would never be faster than the cascading and could be slower as your code might compromise the optimisation of the compiler.
That is what I thought.. Thanks Brian..
 

Users who are viewing this thread

Top Bottom