Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 06-11-2019, 04:14 PM
Klaymen's Avatar
Klaymen Klaymen is offline
Member
CAS
 
Join Date: Oct 2001
Studying for this space for rent
Posts: 20,012
Default Access (SQL) question

POLICY_NUMBER REPORT_CUT_OFF_DATE SumOfPOINTS
CA-166280 1/26/2014 1
CA-166280 1/25/2015 1
CA-166280 1/24/2016 1
CA-166280 1/22/2017 1
CA-166280 2/5/2017 -100

I'm a bit out of practice with Access (YES, ACCESS)
I want to know if a policy is still active, so I add one point for a renewal and subtract 100 for a cancellation.

How do I get Access to add up the points before or equal to each report cut off date? I want this to look like:

CA-166280 1/26/2014 1
CA-166280 1/25/2015 2
CA-166280 1/24/2016 3
CA-166280 1/22/2017 4
CA-166280 2/5/2017 -96

I currently do this on a second machine with Excel using a basic SUMIF and I'm trying to do it more efficiently. If you can call this that.
__________________



Last edited by Klaymen; 06-11-2019 at 04:24 PM..
Reply With Quote
  #2  
Old 06-11-2019, 04:27 PM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,835
Default

in sql server
Code:
create table #t
(POLICY_NUMBER varchar(9),
REPORT_CUT_OFF_DATE datetime,
SumOfPOINTS integer)

insert into #t (POLICY_NUMBER,REPORT_CUT_OFF_DATE,SumOfPOINTS) values ('CA-166280','01/26/2014',1)
insert into #t (POLICY_NUMBER,REPORT_CUT_OFF_DATE,SumOfPOINTS) values ('CA-166280','01/25/2015',1)
insert into #t (POLICY_NUMBER,REPORT_CUT_OFF_DATE,SumOfPOINTS) values ('CA-166280','01/24/2016',1)
insert into #t (POLICY_NUMBER,REPORT_CUT_OFF_DATE,SumOfPOINTS) values ('CA-166280','01/22/2017',1)
insert into #t (POLICY_NUMBER,REPORT_CUT_OFF_DATE,SumOfPOINTS) values ('CA-166280','02/05/2017',-100)

select * from #t

select a.POLICY_NUMBER, a.REPORT_CUT_OFF_DATE, SumOfPOINTS,
  (select sum(SumOfPOINTS) from #t where POLICY_NUMBER=a.POLICY_NUMBER and REPORT_CUT_OFF_DATE<=a.REPORT_CUT_OFF_DATE) as running_pts
  from #t a

drop table #t
not sure about the visual building of queries in access as i haven't used it in years
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.
Reply With Quote
  #3  
Old 06-11-2019, 09:43 PM
rebelarc rebelarc is offline
Member
CAS SOA
 
Join Date: Dec 2013
College: Aspiring Excel Monkey
Posts: 82
Default

You can inner join the table to itself on Policy_Number (you will have 2 tables to work with, ex. Table A and Table A1), then you can select the following:

[Table A].[Policy_Number] (group by)
[Table A].[Report_Cutoff_Date] (group by)
[Table A1].[Points] (sum)
[Table A1].[Report_Cutoff_Date] (where <= [Table A].[Report_Cutoff_Date])
Reply With Quote
  #4  
Old 06-13-2019, 07:13 AM
Steve Grondin Steve Grondin is online now
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 6,856
Default

Quote:
Originally Posted by Klaymen View Post
I currently do this on a second machine with Excel using a basic SUMIF and I'm trying to do it more efficiently. If you can call this that.
Sum if? If remaining in excel is an option, sort the data by policy-no and date, then use an if to start tally at new policyno.

In acess, use the le join condition described above.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 06:50 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.23484 seconds with 9 queries