Link to home
Create AccountLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

sql server query to access sql help

ACCESS 2010
sql server query to access -  linked tables

i'm trying to update a table , it keeps telling me my FROM statement is not correct ?
this query is from sql server....putting it in access..
 update fix
      set fix.[Aligned CSM/MAM] = nata_use.[Aligned CSM/MAM]
    , fix.[Director Name]= nata_use.[Director Name]
        , fix.[am_arm_racf]= nata_use.[am_arm_racf]
    , fix.[director_dsm_racf] = nata_use.[director_dsm_racf]
    , fix.[NSC_Segment] = nata_use.[NSC_Segment]
  FROM [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] fix
  ,[ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] nata_use
  ,[ss_program_workflow].[dbo].[t_nsc_trackcode_reassign_overrides] ntra
  where 1 = 1
  and fix.[Track_code] = @curr_track   '' from text
  and ntra.[Current Track_Code] = @curr_track  ' from text
  and nata_use.[Track_Code] = @realign_to_track  ' to text

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

I see three tables in your FROM clause but I don't see two JOIN .. ON clauses stating how those tables are related.
Avatar of Fordraiders

ASKER

jim.
“dbo_t_nsc_trackcode_reassign_overrides”
Realign_Using_Trackcode
Current Track_Code

To
“dbo_t_nsc_trackcode_assigned”
Track_code

join
Current Track_Code = Track_code

fordraiders
Replace the ???'s with whatever columns relate these tables..
UPDATE fix
SET 
	fix.[Aligned CSM/MAM] = nata_use.[Aligned CSM/MAM], 
	fix.[Director Name]= nata_use.[Director Name], 
	fix.[am_arm_racf]= nata_use.[am_arm_racf], 
	fix.[director_dsm_racf] = nata_use.[director_dsm_racf], 
	fix.[NSC_Segment] = nata_use.[NSC_Segment]
FROM [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] fix
   JOIN [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] nata_use ON fix.??? = nata_use.???
	JOIN [ss_program_workflow].[dbo].[t_nsc_trackcode_reassign_overrides] ntra ON nata_use.??? = ntra.???
WHERE 
	fix.[Track_code] = @curr_track AND 
	ntra.[Current Track_Code] = @curr_track AND
	nata_use.[Track_Code] = @realign_to_track  

Open in new window

jim this is my attempt in access  (no joins yet)
strsql_sql = ""
                    strsql_sql = "UPDATE [dbo_t_nsc_trackcode_assigned]" & vbCrLf
                    strsql_sql = strsql_sql & " SET [dbo_t_nsc_trackcode_assigned].[Aligned CSM/MAM] = [dbo_t_nsc_trackcode_assigned].[Aligned CSM/MAM]" & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[director_dsm_racf] = [dbo_t_nsc_trackcode_assigned].[director_dsm_racf] " & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[am_arm_racf] = [dbo_t_nsc_trackcode_assigned].[am_arm_racf]" & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[director_dsm_racf] = [dbo_t_nsc_trackcode_assigned].[director_dsm_racf]" & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[NSC_Segment] = [dbo_t_nsc_trackcode_assigned].[NSC_Segment] " & vbCrLf
                    strsql_sql = strsql_sql & " FROM [dbo_t_nsc_trackcode_assigned] AND [dbo_t_nsc_trackcode_reassign_overrides] " & vbCrLf
                    strsql_sql = strsql_sql & " WHERE 1 = 1 " & vbCrLf
                    strsql_sql = strsql_sql & " AND [dbo_t_nsc_trackcode_assigned].[Track_Code] = '" & txtfrom & "' " & vbCrLf
                    strsql_sql = strsql_sql & " AND [dbo_t_nsc_trackcode_reassign_overrides].[Current Track_Code] = '" & txtfrom & "' " & vbCrLf
                    strsql_sql = strsql_sql & " AND [dbo_t_nsc_trackcode_assigned].[Track_Code] = '" & txtto & "';"
                    CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window

User generated image
I'm trying to get the query to work in access


here is the error
Avatar of Arana (G.P.)
Arana (G.P.)

"FROM [dbo_t_nsc_trackcode_assigned] AND [dbo_t_nsc_trackcode_reassign_overrides]"

That is wrong, you cannot use AND after FROM, you are MIXING the where clause with the FROM,
maybe just change your AND with a comma
If you don't know how to write Access syntax (it really isn't much different from T-SQL), then use the query designer.  Paste in the select clause, the from clause with no joins in SQL View.  Switch to QBE view and draw the join lines.  Convert the query type to update and choose the fields in the update to line.

I don't know what Access' rule is (I don't have it loaded to check) but you might need "as" in front of each Alias for the tables.
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
THANKS ALL