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

Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
Old 12-22-2008, 12:18 PM
Gates Is Antichrist's Avatar
Gates Is Antichrist Gates Is Antichrist is offline
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

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 12:19 PM.

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