update table from another table

spinkung

Registered User.
Local time
Today, 00:44
Joined
Dec 4, 2006
Messages
267
hi all

i'm trying to update a table with the values from another table. In SQL it works as in my example but it doen't like this in access.

it has to be in vba as the source table is dynamically created.

Code:
sql = "update a " & _
        "set a.Jan_FVol = b.Jan_FC, " & _
        "a.Feb_FVol = b.Fec_FC, " & _
        "a.Mar_FVol = b.Mar_FC, " & _
        "a.Apr_FVol = b.Apr_FC, " & _
        "a.May_FVol = b.May_FC, " & _
        "a.Jun_FVol = b.Jun_FC, " & _
        "a.Jul_FVol = b.Jul_FC, " & _
        "a.Aug_FVol = b.Aug_FC, " & _
        "a.Sep_FVol = b.Sep_FC, " & _
        "a.Oct_FVol = b.Oct_FC, " & _
        "a.Nov_FVol = b.Nov_FC, " & _
        "a.Dec_FVol = b.Dec_FC " & _
        "from CBF_master a " & _
        "inner join " & tmpTbl & " b " & _
        "on a.Year = b.dt_year " & _
        "and a.Customer = b.Customer " & _
        "and a.StockCode = b.StockCode"

am i doing it wrong??

Thanks
 
there is no duplication (i hope)

i have a forecast table. i load a customers excel file into a temp table. then once the account manager has made any changes to the tmp table i want to update the forecast table with the temp table values where the year, customer, stockcode match.

should the below achieve this or do i need to be looking at a different method?

thanks
 
Howzit

It may very well be slight syntax differences between access SQL and SQL

Try

Code:
sql = "update CBF_master AS a " & _
        "inner join " & tmpTbl AS b " & _
        "on a.Year = b.dt_year " & _
        "and a.Customer = b.Customer " & _
        "and a.StockCode = b.StockCode " _
        "set a.Jan_FVol = b.Jan_FC, " & _
        "a.Feb_FVol = b.Fec_FC, " & _
        "a.Mar_FVol = b.Mar_FC, " & _
        "a.Apr_FVol = b.Apr_FC, " & _
        "a.May_FVol = b.May_FC, " & _
        "a.Jun_FVol = b.Jun_FC, " & _
        "a.Jul_FVol = b.Jul_FC, " & _
        "a.Aug_FVol = b.Aug_FC, " & _
        "a.Sep_FVol = b.Sep_FC, " & _
        "a.Oct_FVol = b.Oct_FC, " & _
        "a.Nov_FVol = b.Nov_FC, " & _
        "a.Dec_FVol = b.Dec_FC "
 
thanks for the reply...

Code:
sql = "update CBF_master as a " & _
        "inner join " & tmpTbl  & " as b " & _
        "on a.Year = b.dt_year " & _
        "and a.Customer = b.Customer " & _
        "and a.StockCode = b.StockCode " _
        [COLOR="Red"]"set a.Jan_FVol = b.Jan_FC, " & _[/COLOR]
        "a.Feb_FVol = b.Fec_FC, " & _
        "a.Mar_FVol = b.Mar_FC, " & _
        "a.Apr_FVol = b.Apr_FC, " & _
        "a.May_FVol = b.May_FC, " & _
        "a.Jun_FVol = b.Jun_FC, " & _
        "a.Jul_FVol = b.Jul_FC, " & _
        "a.Aug_FVol = b.Aug_FC, " & _
        "a.Sep_FVol = b.Sep_FC, " & _
        "a.Oct_FVol = b.Oct_FC, " & _
        "a.Nov_FVol = b.Nov_FC, " & _
        "a.Dec_FVol = b.Dec_FC"

it's falling over on the red line??
 
ahh good spot

it's still failing on the same line though.

getting an : Expected end of statement error??
 
Howzit

Try with bracketing

Code:
sql = "update CBF_master as a " & _
        "inner join " & tmpTbl  & " as b " & _
        "on (a.Year = b.dt_year) " & _
        "and (a.Customer = b.Customer) " & _
        "and (a.StockCode = b.StockCode) " & _
        "Set a.Jan_FVol = b.Jan_FC, " & _
        "a.Feb_FVol = b.Fec_FC, " & _
        "a.Mar_FVol = b.Mar_FC, " & _
        "a.Apr_FVol = b.Apr_FC, " & _
        "a.May_FVol = b.May_FC, " & _
        "a.Jun_FVol = b.Jun_FC, " & _
        "a.Jul_FVol = b.Jul_FC, " & _
        "a.Aug_FVol = b.Aug_FC, " & _
        "a.Sep_FVol = b.Sep_FC, " & _
        "a.Oct_FVol = b.Oct_FC, " & _
        "a.Nov_FVol = b.Nov_FC, " & _
        "a.Dec_FVol = b.Dec_FC"
 
Howzit

Access does have subtle differences to T-SQL.

Glad to hear it is working.
 

Users who are viewing this thread

Back
Top Bottom