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 12-22-2008, 12:18 PM
Gates Is Antichrist's Avatar
Gates Is Antichrist Gates Is Antichrist is offline
Member
SOA AAA
 
Join Date: Feb 2002
Location: Hell
Favorite beer: Pacifico
Posts: 161
Default Want Access timesaver trick to change source table

I normally use the design window for queries rather than the native SQL view. Over 90% of the time there's only one source table. Sometimes I want to run the exact same query but merely change the source table name, where the new source table has identical field names, but perhaps is for a different business segment or date.

If there are numerous query fields, to change the table in the design window is tedious. I first add the table to the top pane (which can be done somewhat handily with the keyboard), but the bad part is changing the table on each field. This can be sped up enormously if the new source table begins with a different letter such as G, allowing you to type G-tab-G-tab-G-tab-G...; but usually the first letter is the same. Note that you must keep both tables in the top pane until the changes are done; if you delete the old source too soon, the field columns disappear.

So I've tended to instead work straight in the SQL view. It would be great if I could just change the FROM line, e.g. changing Sales2007 to Sales2008. The hitch is having all those "Sales2007." prefixes on every field reference. With many-field queries, this is so bad that I (A)select the entire SQL, (B)Open Word or some editor, (C)open the editor replace window and type in "Sales2007." as what to replace, and nothing (empty string) as replacement. (D) replace all, (E) select all, (F) paste back to the Access SQL window, (G)revert to Design view.

Sadly I've gotten really fast at this since at least I can do it with 100% keyboard - but can it further simplified? Can I prohibit those table qualifiers (Sales2007.) from being stuck in there when switching from design view to SQL view?

(BTW, building SQL strings and running from VBA is one angle, but I'd prefer to have saved, editable queries.)
__________________
______
ON STRIKE. Thomas Jefferson and I are on strike against AO over a little matter called the First Amendment and censorship against dissenting views. I hereby choose to toss my voluntary contributions into the Harbor rather than accede in slavery to the oppressor.

Last edited by Gates Is Antichrist; 12-22-2008 at 12:27 PM.. Reason: Fix A-G "enumeration"
Reply With Quote
  #2  
Old 12-22-2008, 01:00 PM
JUICE JUICE is offline
Member
SOA AAA
 
Join Date: Sep 2007
Favorite beer: Bell's Two Hearted
Posts: 9,826
Default

Open query in design view. Eliminate any entries in the 'table' field. Just delete them, it will still work as long as the field names aren't ambiguous.
Click on tools, options, general. Uncheck "Track name AutoCorrect info"
Right click the show table area, click add table, add the new table. right click the old table, remove it.
Voila!
Reply With Quote
  #3  
Old 12-22-2008, 04:45 PM
Gates Is Antichrist's Avatar
Gates Is Antichrist Gates Is Antichrist is offline
Member
SOA AAA
 
Join Date: Feb 2002
Location: Hell
Favorite beer: Pacifico
Posts: 161
Default

Quote:
Originally Posted by JUICE View Post
Open query in design view. Eliminate any entries in the 'table' field. Just delete them, it will still work as long as the field names aren't ambiguous.
Click on tools, options, general. Uncheck "Track name AutoCorrect info"
Right click the show table area, click add table, add the new table. right click the old table, remove it.
Voila!
It would be nice if someone has an instant way to force prefixing to be by alias in the SQL, yielding e.g. SELECT A.fld1, A.fld2,... FROM MyTable as A; so I could just change MyTable to MyTable2 once in SQL. But delete/tab/delete/tab... in Design isn't horrible. Thanks for that suggestion.

BTW, Name autocorrect (pretty well covered at Microsoft) can stay on, at least as far as I see. Is there a circumstance where ignoring that step can burn you? I gather that Name tracking only matters with regard to renaming something.
__________________
______
ON STRIKE. Thomas Jefferson and I are on strike against AO over a little matter called the First Amendment and censorship against dissenting views. I hereby choose to toss my voluntary contributions into the Harbor rather than accede in slavery to the oppressor.
Reply With Quote
  #4  
Old 12-24-2008, 04:52 PM
Old Timer's Avatar
Old Timer Old Timer is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: The Great East
Posts: 1,984
Default

How about using a simple table name in the query like Sales. Then copy Sales2007 and rename it as Sales. When you need Sales2008, copy it over Sales.
__________________
Life is chaos personified.
Reply With Quote
  #5  
Old 12-24-2008, 05:21 PM
GatesIsAntichrist's Avatar
GatesIsAntichrist GatesIsAntichrist is offline
Member
SOA AAA
 
Join Date: Mar 2005
Location: Hell (on temporary assignment on Earth)
Favorite beer: New(fave)! Black'n'Tan
Posts: 206
Default

That's so easy it feels like cheating. Nice thinking!

I might think about compacting more often when I'm doing that.
Reply With Quote
  #6  
Old 12-24-2008, 09:39 PM
GadgetGeek's Avatar
GadgetGeek GadgetGeek is offline
Note Contributor
SOA AAA
 
Join Date: Sep 2001
Location: I'm here, where are you?
Studying for a way to escape
College: Illinois College ('87)
Favorite beer: Stag
Posts: 11,154
Default

Quote:
Originally Posted by Old Timer View Post
How about using a simple table name in the query like Sales. Then copy Sales2007 and rename it as Sales. When you need Sales2008, copy it over Sales.
A variation...create a linked table called sales and just change the link to Sales2007 or Sales2008 or whatever.
__________________
Some people are like slinkies. Mostly useless, but provide amusement when pushed down the stairs.
Reply With Quote
  #7  
Old 12-24-2008, 09:45 PM
GatesIsAntichrist's Avatar
GatesIsAntichrist GatesIsAntichrist is offline
Member
SOA AAA
 
Join Date: Mar 2005
Location: Hell (on temporary assignment on Earth)
Favorite beer: New(fave)! Black'n'Tan
Posts: 206
Default

Quote:
Originally Posted by GadgetGeek View Post
A variation...create a linked table called sales and just change the link to Sales2007 or Sales2008 or whatever.
Agreed. That's good.
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:39 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, 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.23498 seconds with 11 queries