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

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

Reply
 
Thread Tools Search this Thread Display Modes
  #21  
Old 12-16-2019, 11:03 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Quote:
Originally Posted by exponentc View Post
If your request can be understood by computers and translated into sql code, I guess that would make things a lot easier.
I guess I don't understand your point.
Reply With Quote
  #22  
Old 12-16-2019, 11:29 PM
exponentc exponentc is offline
Member
CAS
 
Join Date: Oct 2018
College: JHU
Posts: 174
Default

Quote:
Originally Posted by 1695814 View Post
I guess I don't understand your point.
I know some people are working on text-to-sql translation. Your request "what policies lost ADB rider in the past month" or so probably can be easily translated into sql code by machines in the future.
https://yale-lily.github.io/spider https://yale-lily.github.io/sparc

Last edited by exponentc; 12-16-2019 at 11:32 PM.. Reason: provide more resource
Reply With Quote
  #23  
Old 12-17-2019, 08:53 AM
glassy glassy is offline
Member
Non-Actuary
 
Join Date: May 2015
Posts: 5,655
Default

Quote:
Originally Posted by 1695814 View Post


I have a table called Valuation. It has the fields Policy, Coverage, & Company.
I have another table called Reserves. It has two fields, UniqueID and StatRes.
UniqueID is a concatenation of Policy, Coverage, & Company

So, here's one sample of data:

Valuation
Policy = ABC001
Coverage = 1
Company = XYZ

Reserves
UniqueID = ABC0011XYZ
StatRes= 42.00


I want to join those two fields, but I don't know how to set it up.
In SAS I would add a field to Valuation called UniqueID = Policy !! Coverage !! Company;
Then I could merge the two by UniqueID.
I don't know how to set that up in Access or SQL.

I think you want

Code:
select Valuation.*, Reserves.*
from Valuation
inner join Reserves on Reserves.UniqueID = (Valuation.Policy + CONVERT(varchar, Valuation.Coverage) + Valuation.Company)

But note that this will only work if Coverage is a single digit. Otherwise you'll have to edit the convert statement.
Reply With Quote
  #24  
Old 12-17-2019, 09:47 AM
Steve Grondin Steve Grondin is online now
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 7,322
Default

Quote:
Originally Posted by glassy View Post
I think you want

Code:
select Valuation.*, Reserves.*
from Valuation
inner join Reserves on Reserves.UniqueID = (Valuation.Policy + CONVERT(varchar, Valuation.Coverage) + Valuation.Company)

But note that this will only work if Coverage is a single digit. Otherwise you'll have to edit the convert statement.
I have a dislike for the ambiguity of the + operator in certain languages. I prefer to have a distinct concat operator from arithmetic operator.

Here the idea is you can only concat char type variables, so converting to char (or varchar ) is necessary.
Reply With Quote
  #25  
Old 12-17-2019, 01:43 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Quote:
Originally Posted by glassy View Post
I think you want
...
Thank you. I ended up having to use the CStr fn because CONVERT gave me some weird error message that I've already forgotten.

Otherwise, that worked wonderfully.

Last edited by 1695814; 12-17-2019 at 02:37 PM..
Reply With Quote
  #26  
Old 12-17-2019, 05:47 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default



So, I have all this lovely SQL code written, and for the sake of readability I like to have it formatted (spaced & indented & such), but it seems that Access does not want someone to do that. It trims & condenses the code so that it's all one big block of text (after running the code, at least).

Is there any way to format the SQL stuff? (Right now, I save a copy of the stuff in Notepad...good ol' notepad...nuthin' beats that)
Reply With Quote
  #27  
Old 12-17-2019, 06:40 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 280
Default

Quote:
Originally Posted by 1695814 View Post


So, I have all this lovely SQL code written, and for the sake of readability I like to have it formatted (spaced & indented & such), but it seems that Access does not want someone to do that. It trims & condenses the code so that it's all one big block of text (after running the code, at least).

Is there any way to format the SQL stuff? (Right now, I save a copy of the stuff in Notepad...good ol' notepad...nuthin' beats that)
Try scripting it in VBA... DoCmd.RunSQL ... you can format to your liking in the ide.
Reply With Quote
  #28  
Old 12-19-2019, 01:39 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Quote:
Originally Posted by IANAE View Post
Try scripting it in VBA... DoCmd.RunSQL ... you can format to your liking in the ide.
You skipped a step for me. Where do I enter/put that DoCmd.RunSQL?
Reply With Quote
  #29  
Old 12-19-2019, 01:43 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

In SAS I could merge two files and if one file didn't have a match I could have it default to something else.

Example:
Code:
DATA MERGE;
 MERGE DATAA(IN=A) DATAB(IN=B);
  BY POLICY;
 IF A;
 IF NOT B THEN CLS = '999';
RUN;
How would something like that be set up in Access or SQL?
Reply With Quote
  #30  
Old 12-19-2019, 02:34 PM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 68,233
Default

Quote:
Originally Posted by 1695814 View Post
In SAS I could merge two files and if one file didn't have a match I could have it default to something else.

Example:
Code:
DATA MERGE;
 MERGE DATAA(IN=A) DATAB(IN=B);
  BY POLICY;
 IF A;
 IF NOT B THEN CLS = '999';
RUN;
How would something like that be set up in Access or SQL?
Note that w/o the indicated line, this code would still do what's expected but leave CLS as a NULL string.

Within Access or SQL, you'd do either a left outer join or a right outer join.

For example, A LEFT OUTER JOIN B would include all records in A and matching records in B, with "unmatched" records containing missing values.
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
Reply

Tags
access

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 01:10 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, 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.18300 seconds with 10 queries