SQL Problem

[Mark Gambo]

I have a table (tblGroupCases) where I am attempting to update a particular field, the problem is that in order to do this I have to join three tables. Two of these tables (Officers and Assoc_Inc_Off) are located in a different MS SQL db. I was thinking about creating a view joining the first two tables then using vb code to join the View and the table that I want to update. My question is can I join three tables in an Update query?
The following is my failed attempt, tblGroupCases is the table I want to update with the data contained in the Officers table but I need to connect these two (2) table via the Assoc_Inc_Officer Table:

strSQL = "UPDATE GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases "
            strSQL = strSQL & "SET GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.Subject_Officer = "
            strSQL = strSQL & "IADATA.DBO.OFFICERS.LNAM "
            strSQL = strSQL & "FROM GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases, "
            strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF, IADATA.DBO.OFFICERS "
            strSQL = strSQL & "WHERE GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.INCNUM =  IADATA.DBO.ASSOC_INC_OFF.INCNUM "
            strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF.INCNUM = IADATA.DBO.Officers.INCNUM;"

If I remove the last line:

strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF.INCNUM = IADATA.DBO.Officers.INCNUM;"

The code runs but it doesn't update the field.
Thank you,

[brucevde]

Other than missing an And in your Where clause the syntax of your sql statement is correct. Are you getting an error message? The only thing I can think of that might stop the statement from executing is permissions.

[Mark Gambo]

Bruce,
Thanks for your reply. I don't beleive that their is a permissions issue because I am able execute other update queries and make table queries. As for the missing AND would I putting it between the two where statements?

strSQL = strSQL & "WHERE GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.INCNUM =  IADATA.DBO.ASSOC_INC_OFF.INCNUM AND"
strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF.INCNUM = IADATA.DBO.Officers.INCNUM;"

Thanks again!

[brucevde]

Yes, you must separate each criteria in the Where clause with an operator, such as And, Or etc..

Probably just a typo but make sure you have a space at the end of the first line (after the And) or at the beginning of the second line (before IAData).

[Mark Gambo]

Bruce,
Thanks, that solved my problem. I modified the sql as follows:

strSQL = "UPDATE GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases "
            strSQL = strSQL & "SET GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.Subject_Officer = "
            strSQL = strSQL & "GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.Subject_Officer  + " & vbCrLf & " + "
            strSQL = strSQL & "IADATA.DBO.OFFICERS.TITLE + ' ' + IADATA.DBO.OFFICERS.FNAM + ' ' + IADATA.DBO.OFFICERS.LNAM, "
            strSQL = strSQL & "GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.Subject_Officer_Cnt =  "
            strSQL = strSQL & "GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.Subject_Officer_Cnt + 1 "
            strSQL = strSQL & "FROM GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases, "
            strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF, IADATA.DBO.OFFICERS "
            strSQL = strSQL & "WHERE GROUP_MANAGEMENT.GROUP_MGMT.tblGroupCases.INCNUM =  IADATA.DBO.ASSOC_INC_OFF.INCNUM AND "
            strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF.OFFNUM = IADATA.DBO.Officers.OFFNUM AND "
            strSQL = strSQL & "IADATA.DBO.ASSOC_INC_OFF.LINK_TYPE = 'OFFICER';"

The Officer Table may contain one or more related records to the tblGroupCase table and I want to store it in the Subject_Officer Field. When I run the query the first value in the Officers Table is being added to the tblGroupCases Table and the additional data is not appended. The additional data meets the same criteria as the appended data and if I run the query multiple times the same name will be appended again and again.

I have been able to create an Update Query in Access using the same data and I am able to format the data in the manner I want it:

UPDATE GROUP_MGMT_tblGroupCases INNER JOIN (dbo_ASSOC_INC_OFF INNER JOIN dbo_OFFICERS ON dbo_ASSOC_INC_OFF.OFFNUM = dbo_OFFICERS.OFFNUM) ON
GROUP_MGMT_tblGroupCases.INCNUM = dbo_ASSOC_INC_OFF.INCNUM SET 
GROUP_MGMT_tblGroupCases.Subject_Officer = [GROUP_MGMT_tblGroupCases]![Subject_Officer] & [dbo_OFFICERS]![Title] & " " & [dbo_OFFICERS]![FNAM] & " " & 
[dbo_OFFICERS]![LNAM] & Chr(13) & Chr(10), GROUP_MGMT_tblGroupCases.Subject_Officer_Cnt = 
[GROUP_MGMT_tblGroupCases]![Subject_Officer_Cnt]=[GROUP_MGMT_tblGroupCases]![Subject_Officer_Cnt]+"1";

Thanks!

[Mark Gambo]

Bump