By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,863 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,863 IT Pros & Developers. It's quick & easy.

Does update query execute multiple times for the same field?

JKing
Expert 100+
P: 1,206
I'm using an update query to update a single field in a table. Table1 is part of my normalized table structure. Table2 is used solely as an import table for raw data which I sort and summarize through queries and VBA routines.

Table I'm updating looks like this
Table1
Code Name
1 Gym
2 Math
3 English

Table I'm using for update looks like this
Table2
Code Name year
1 Physical Ed. 2007
1 Physical Ed. 2007
1 Physical Ed. 2007
1 Gym 2005
1 Gym 2005

My update checks for the latest year and will update any Names in table1 where the Name is not equal to the name in Table2 with the same code. The update runs fine and gym will be updated to Physical Ed. in table1 however it will say it's updating 3 records instead of one. Does the query actually update the same field 3 times? Or does it break once the first update executes?
Jun 21 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,430
We'd have to look at the SQL you're using to determine that.
Jun 21 '07 #2

JKing
Expert 100+
P: 1,206
tblProgram is the table im updating to
tblStudentImport is the table with raw data

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblStudentImport INNER JOIN tblProgram ON tblStudentImport.progCode = tblProgram.progCode SET tblProgram.progName = [tblStudentImport].[progName]
  2. WHERE (((tblProgram.progName)<>[tblStudentImport].[progName]) AND ((tblStudentImport.year)=(SELECT MAX(year) FROM tblStudentImport)))
  3.  
My worry is that it executes several times doing the same thing. I originally wrote the update query basing it off a seperate query that selected the distinct fields from the raw data table. It would produce only the single update in the datasheet view however when I went to run I was given the error that "operation must use an updateable query". So to work around that I've moved to the above solution.
Jun 21 '07 #3

Rabbit
Expert Mod 10K+
P: 12,430
tblProgram is the table im updating to
tblStudentImport is the table with raw data

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblStudentImport INNER JOIN tblProgram ON tblStudentImport.progCode = tblProgram.progCode SET tblProgram.progName = [tblStudentImport].[progName]
  2. WHERE (((tblProgram.progName)<>[tblStudentImport].[progName]) AND ((tblStudentImport.year)=(SELECT MAX(year) FROM tblStudentImport)))
  3.  
My worry is that it executes several times doing the same thing. I originally wrote the update query basing it off a seperate query that selected the distinct fields from the raw data table. It would produce only the single update in the datasheet view however when I went to run I was given the error that "operation must use an updateable query". So to work around that I've moved to the above solution.
Yes, you're repeating the operation multiple times. It's because of the inner join, it's creating duplicate records. Rather than an inner join, try using a subquery that selects top 1 instead.

Actually, you're going to have multiple top 1's... use DLookup(DMax()) instead.
Jun 21 '07 #4

JKing
Expert 100+
P: 1,206
I've been attempting to use DLookup(Dmax()) and I can't seem to get it quite right. I removed the inner join I'm attempting to use the function in the where clause as follows:

Expand|Select|Wrap|Line Numbers
  1. AND tblProgram.progCode = DLookup("progCode", "tblStudentImport", "progCode ='" & DMax("progCode", "tblStudentImport", "progCode ='" & tblProgram.progCode & "'") & "'")
  2.  
I think the criteria of DMax is the part I can't seem to figure out. I've tried different values and it either returns every progName, nothing or I get an error/syntax message. Thanks for looking at this.
Jun 21 '07 #5

Rabbit
Expert Mod 10K+
P: 12,430
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblProgram
  2. SET tblProgram.progName = DLookup("progName", "tblStudentImport", "year = " & DMax("year", "tblStudentImport", "progName = '" & tblProgram.progName & "'"));
Jun 21 '07 #6

JKing
Expert 100+
P: 1,206
That makes alot more sense. Wasn't quite sure where the DLookup needed to be or what field it had to be on having never used it before. I'll try it out when I'm back in the office next week. Thanks in advance.
Jun 21 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.