We help IT Professionals succeed at work.

sql server query to access sql help

99 Views
Last Modified: 2018-12-12
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

Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

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

Author

Commented:
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
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Author

Commented:
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

Author

Commented:
error on update
I'm trying to get the query to work in access


here is the error
CERTIFIED EXPERT

Commented:
"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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Consulting
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
THANKS ALL

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions