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

DW Simpson Global Actuarial & Analytics Recruitment
Download our Actuarial Salary Survey
now with state-by-state salary information!


Reply
 
Thread Tools Search this Thread Display Modes
  #61  
Old 03-18-2020, 12:22 PM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 3,366
Default

Quote:
Originally Posted by 1695814 View Post
I have query that joins two tables, ReservesCurr & ReservesPrev, by Policy.
In each table is a field called "Reserve".

In my output, it is (automatically) labeling those "Reserve" & "Reserve1".

I'd rather have it be something more descriptive. Even ReservesCurr.Reserve & ReservesPrev.Reserve would be useful.

I don't know how to do that.
In the query design screen, you can assign new field names to the output of the query

In the top row, one cell might read "ReservesCurr: [ReservesCurr].[Reserve]", while another cell might read "ReservesPrev: [ReservesPrev].[Reserve]".

By doing that, you assign field name "ReservesCurr" to the "Reserves" value coming from table ReservesCurr, and you assign field name "ReservesPrev" to the other one.

This tactic also comes in handy when doing aggregate queries, and you don't want automatic field names like "SumOfReserves".
Reply With Quote
  #62  
Old 03-31-2020, 05:08 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 38,999
Default

I get "Enter Parameter Value" pop up boxes when I run this query:
Quote:
SELECT [ValuationPrev].TYPE
, [ValuationCurr].TYPE
, [ValuationPrev].CO
, [ValuationCurr].CO
, [ValuationPrev].LOB
, [ValuationCurr].LOB
, Sum([ValuationPrev].PREMIUM) AS SumOfPREMIUM
, Sum([ValuationCurr].PREMIUM) AS SumOfPREMIUM1
, Sum(IIf([ValuationPrev].[BASE]=1,[CtPrev]=1,[CtPrev]=0)) AS Expr1
, Sum(IIf([ValuationCurr].[BASE]=1,[CtCurr]=1,[CtCurr]=0)) AS Expr2


FROM [ValuationCurr] INNER JOIN [ValuationPrev] ON [ValuationCurr].POLICY = [ValuationPrev].POLICY

GROUP BY [ValuationPrev].TYPE, [ValuationCurr].TYPE, [ValuationPrev].CO, [ValuationCurr].CO, [ValuationPrev].LOB, [ValuationCurr].LOB;
The box notes "CtPrev" & then "CtCurr". I imagine it's the bolded above that's causing it. I don't previously have a variable called "CtPrev" nor "CtCurr". Do those need to be initialized/formatted/somethinged?

What I'm trying to do here is, merge/join records from the previous & current valuation files. For those policies where "[BASE]" is equal to one, that means the Count s/b equal to one. Otherwise, it should be zero.

I imagine I just don't have the syntax correct.

Last edited by 1695814; 03-31-2020 at 05:45 PM..
Reply With Quote
  #63  
Old 04-01-2020, 03:32 PM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 13,203
Default

, Sum(IIf([ValuationPrev].[BASE]=1,1,0)) AS [CtPrev]
, Sum(IIf([ValuationCurr].[BASE]=1,1,0)) AS [CtCurr]
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.
Reply With Quote
  #64  
Old 04-01-2020, 06:18 PM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 7,291
Default

Quote:
Originally Posted by 1695814 View Post
I get "Enter Parameter Value" pop up boxes when I run this query:

The box notes "CtPrev" & then "CtCurr". I imagine it's the bolded above that's causing it. I don't previously have a variable called "CtPrev" nor "CtCurr". Do those need to be initialized/formatted/somethinged?

What I'm trying to do here is, merge/join records from the previous & current valuation files. For those policies where "[BASE]" is equal to one, that means the Count s/b equal to one. Otherwise, it should be zero.

I imagine I just don't have the syntax correct.
Yes. Access is saying I don't know what CtPrev is. It is expecting an expression where you typed CtPrev = 1, so it said "aha a boolean! what is CtPrev?", even though you thought you were giving it an assignment statement.

It look like you want a summary on all matches where old.policyno = new.policyno, grouping by the old.type, new.type, old.co, new.co, old.lob, new.lob. For each grouping you want the total of the old.premium and new.premium and two "new" variables. One is the count of (old.base =1), the other is the count of (new.base = 1).

Meshuga's answer is right. the AS syntax is where you are giving the new column a name. Note that the name is just a display feature within this query (if it were nested, then you could use the name in a higher level). Personally, I would change the two lines above to read AS SumPrevPremium AS SumCurrPremium instead of SumPremium, SumPremium1.
Reply With Quote
  #65  
Old 04-02-2020, 12:51 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 38,999
Default

Is there an easy way to re-order fields in a query?

Right now I have a query that summarizes premium, face, and count by LOB, but that I really want is a summary of count, premium, and face by LOB.

Access doesn't act like Excel insofar as moving stuff around. I'll probably just resort to messing with the SQL code.
Reply With Quote
  #66  
Old 04-02-2020, 01:49 PM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 7,291
Default

Quote:
Originally Posted by 1695814 View Post
Is there an easy way to re-order fields in a query?

Right now I have a query that summarizes premium, face, and count by LOB, but that I really want is a summary of count, premium, and face by LOB.

Access doesn't act like Excel insofar as moving stuff around. I'll probably just resort to messing with the SQL code.
If I remember the Access graphical interface (whatever view it is called), you just drag and drop to change order.
Reply With Quote
  #67  
Old 04-03-2020, 12: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: 38,999
Default

If I have a bunch of work books open in Excel, I can close the active one by ctrl+w or alt-f-c.

Is there something similar in Access? I have a bunch of queries (or other objects) open and would like to close the active one without using the mouse.
Reply With Quote
  #68  
Old 05-08-2020, 10:25 AM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 38,999
Default

I inherited a big db that has a bunch of queries & such.


In one of those it creates a variable called INTERPFACTOR that's used in a number of queries at the end.


Is there an easy way to find the query where INTERPFACTOR is created? I'd like to see what goes in to it, but am having trouble finding where it's declared & built & all that.
Reply With Quote
  #69  
Old 05-08-2020, 12:23 PM
Dr T Non-Fan Dr T Non-Fan is online now
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 99,709
Default

Might be a VBA thing?
__________________
"Facebook is a toilet." -- LWTwJO

"45 es un titere" -- Seal of The President of The United States of America protest art

“That is reminiscent of Harry Truman’s famous saying, ‘The buck stops, uh, somewhere over there, maybe?' That is a level of dodging responsibility that Trump has been perfecting ever since he was very much not in Vietnam.” -- LWTwJO
Reply With Quote
  #70  
Old 05-08-2020, 12:53 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 38,999
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
Might be a VBA thing?
Not in this case. I happen to know that there was a previous "Make Table Query" where this variable was defined with an expression.


I'm hoping that there's some way to easily find that query so I can study the expression.
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 05:26 PM.


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.27820 seconds with 10 queries