Solved

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

Posted on 2011-02-23
2
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34961274
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34961309
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

635 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