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
  #31  
Old 12-19-2019, 02:49 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,345
Default

Quote:
Originally Posted by Vorian Atreides View Post
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.
Is there a way in SQL (or Access) to give a default value to "unmatched" records?
Reply With Quote
  #32  
Old 12-19-2019, 03:08 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 100,414
Default

Default value is "{null}" or something similarly unhelpful.
__________________
"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
  #33  
Old 12-19-2019, 03:15 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,546
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
Default value is "{null}" or something similarly unhelpful.
I believe that ### was asking about a way to give a "more helpful" default value from such a merge.
__________________
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
  #34  
Old 12-19-2019, 03:15 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,546
Default

I think you'd have to "subselect" the set with the "unmatched' records and give the default value and merge that set back to the main set.
__________________
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
  #35  
Old 12-19-2019, 03:19 PM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 7,434
Default

You are asking, "can I put a value where a lack of match would produce a null?"

You can do it in the merge itself with a IIF statement in place of a field name in the select statement.

It isn't clear from your question whether the field CLS is in either A or B. I'll pretend it isn't in either, and each field name is distinct. You can mod the a.*, b.* part if this isn't the case. I also am presuming you want all of A and only what matches A from B. That's what makes it a LEFT JOIN (or a LEFT OUTER JOIN). The matching field names matchfield1 need not be the same name, and the match condition can be something other than equality. Make sure you understand what multiples in either file do to the match.

SELECT A.*, B.*,
IIF(B.Field IS NULL,'999',B.field) AS CLS
FROM A LEFT JOIN B
ON A.matchfield1 = B.matchfield1, ...


Note that this method can't distinguish between the cases of B has no match for A and B has a match, but the value of field in B is NULL. If you used B.matchfield1 in the IIF statement instead of B.field you could tell. (this would be more complex for multiple join fields)

Last edited by Steve Grondin; 12-19-2019 at 03:29 PM.. Reason: more
Reply With Quote
  #36  
Old 12-20-2019, 03:07 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,345
Default

This seems obvious, and, based on n=1, I've concluded that the answer is "Yes, of course," but linking a table into a db instead of importing it outright helps keep the size of the db under control, yes?
Reply With Quote
  #37  
Old 12-20-2019, 03:25 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,505
Default

Quote:
Originally Posted by 1695814 View Post
This seems obvious, and, based on n=1, I've concluded that the answer is "Yes, of course," but linking a table into a db instead of importing it outright helps keep the size of the db under control, yes?
The answer is "yes, but".

The file size limit in Access is 2GB (unless they've relaxed that in the latest versions of Access 64bit).

I have had a couple of single-project Access "databases" that have gotten carried away, resulting in my using linked tables of other database files to circumvent the 2GB limit.

By the time you're working with databases where file size is a concern, it's time to look at other tools, corporate IT bureaucracy be damned.
Reply With Quote
  #38  
Old 12-20-2019, 03:44 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 281
Default

Quote:
Originally Posted by Maphisto's Sidekick View Post
By the time you're working with databases where file size is a concern, it's time to look at other tools, corporate IT bureaucracy be damned.
Reply With Quote
  #39  
Old 12-23-2019, 12:06 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,345
Default

The Access db I have has a whole bunch of queries. They are sorted by name. Is there a way to sort them by "last saved" or anything like that?
Reply With Quote
  #40  
Old 12-23-2019, 03:11 PM
Sleeping Dragon Sleeping Dragon is offline
Member
 
Join Date: Jun 2003
Posts: 138
Default

Right Click at the top of the navigation pane, where it says "All Access Objects" or "Queries". It will show the available sort options.
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:48 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.34553 seconds with 10 queries