update table from another table

[spinkung]

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.

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

 

[vbaInet]

I'm just wondering why the duplication?

 

[spinkung]

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

 

[Kiwiman]

Howzit

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

Try

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 "

 

[spinkung]

thanks for the reply...

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??

 

[tehNellie]

The line above is missing an & in the pasted sample.

 

[Kiwiman]

Howzit

Sorry was paying attention.

 

[spinkung]

ahh good spot

it's still failing on the same line though.

getting an : Expected end of statement error??

 

[Kiwiman]

Howzit

Try with bracketing

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"

 

[spinkung]

brilliant. it's working fine now. thanks.

 

[Kiwiman]

Howzit

Access does have subtle differences to T-SQL.

Glad to hear it is working.