Solved

Is there a more efficient way to write the following SQL statement?

Posted on 2011-02-23
2
254 Views
Last Modified: 2012-05-11
I am developing an Access applicatiion using Access 2003 with an MDB type file.

I have a SQL statement that is running inefficiently as follows:

update tblBanks
inner join tblBanksReptNameCurr
On tblBanks.RptID=tblBanksReptNameCurr.[RPT ID]
set tblBanks.[SENIOR MANAGEMENT TAB] = tblBanksReptNameCurr.[SENIOR MANAGEMENT TAB]  AND
tblBanks.CURRENCY = tblBanksReptNameCurr.CURRENCY

Is there a better way to create this SQL statement?
0
Comment
Question by:zimmer9
2 Comments
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

SQL is fine but you should make sure you have indexes set on fields tblBanks.RptID and tblBanksReptNameCurr.[RPT ID] and make sure both thable have primary keys.
Also, save the SQL statement as a query and run the query.

That should speed it up.

Regards,

Bill
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
i wonder if you were able to run the update query that you have...

you don't need the "AND" on this line

set tblBanks.[SENIOR MANAGEMENT TAB] = tblBanksReptNameCurr.[SENIOR MANAGEMENT TAB] AND
tblBanks.CURRENCY = tblBanksReptNameCurr.CURRENCY



update tblBanks
inner join tblBanksReptNameCurr
On tblBanks.RptID=tblBanksReptNameCurr.[RPT ID]
set tblBanks.[SENIOR MANAGEMENT TAB] = tblBanksReptNameCurr.[SENIOR MANAGEMENT TAB] ,
tblBanks.CURRENCY = tblBanksReptNameCurr.CURRENCY


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now