UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Question About Runsql, Access 2010    
 
   
dzoker
post Dec 28 2017, 09:22 AM
Post#1



Posts: 372
Joined: 12-February 14



Without going any deeper in my database setup, is there a way to simplify this code that I'm running in a module:

CODE
DoCmd.RunSQL "UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[1] = [a]WHERE (((tblEmployeeInfo.Platoon)=""a"") AND ((a.autonumber)=1));"
    DoCmd.RunSQL "UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[2] = [a]WHERE (((tblEmployeeInfo.Platoon)=""a"") AND ((a.autonumber)=2));"
    DoCmd.RunSQL "UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[3] = [a]WHERE (((tblEmployeeInfo.Platoon)=""a"") AND ((a.autonumber)=3));"

........
Up until 31 (one for each day of the month). It just takes some time to run this.

Thank you in advance and Happy New Year to all of UtterAccess users fundrink.gif

Go to the top of the page
 
BruceM
post Dec 28 2017, 11:39 AM
Post#2


UtterAccess VIP
Posts: 7,074
Joined: 24-May 10
From: Downeast Maine


I don't understand your SQL. What is a? If anything, this resembles a Cartesian product (query with tables that are not joined) where "a" is the name or a table, but [a] also seems to be a field value.

Aside from that, do you have fields names 1, 2, 3...31 in the Employee table? If so, I assume those are days of the month. If so, the design is not sustainable. Field names should not include data such as the day of the month, month name, etc.

You can probably create a loop based on an integer variable (i) incremented from 1 to the number of days in the month. Each time through the loop, substitute that variable for the numbers:

"UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[" & i & "] = [a] WHERE tblEmployeeInfo.Platoon =""a"" AND a.autonumber = " & i & ";"

However, you should not do that. Your efforts would be better spent normalizing the structure is such a way that the cumbersome series of updates is not needed.

It may help to understand what exactly you are trying to do.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th January 2018 - 02:41 AM