Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Filter on INNER JOIN Query

    Is there a way to FILTER on another field in the TblCARETSOfcRostersAPR10 BEFORE this query runs which will limit the query to only specific records that match the filter - say "*DAMLS*"

    This table has 59,000 + records.

    Code:
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[ListOfficeID] = [TblCARETSOfcRostersAPR10].[OFFICEID] Set [tblCARETSData].[ListOfficeName] = [tblCARETSOfcRostersAPR10].[Officename]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[SaleOfficeID] = [TblCARETSOfcRostersAPR10].[OFFICEID] Set [tblCARETSData].[SaleOfficeName] = [tblCARETSOfcRostersAPR10].[Officename]"
    CurrentDb.Execute strSQL
    It sure would help me out!

    Thanks.

    Rick

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It would be more expensive to generate a "pre filtered" recordset than adding a where clause to your update statement. Are you sure you can't meet your goals by adding a WHERE clause to what you already have?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Adding a WHERE Clause to an INNER JOIN Query

    Would the WHERE clause go just before, or after the"CurrentDb.Execute strSQL" statement?

    Thanks Teddy . . .

    Rick

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It would be part of the string you're assigning to strSQL. You can add a WHERE clause to an update statement just like you can on a normal SELECT statement.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Red face Placement of WHERE

    Here is where I tried putting my WHERE clause . . .

    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[ListOfficeID] = [TblCARETSOfcRostersAPR10].[OFFICEID] Set [tblCARETSData].[ListOfficeName] = [tblCARETSOfcRostersAPR10].[Officename] WHERE [TblCARETSOfcRostersAPR10].[OfficeSubSystemLocal] = "CARETS_SOCALMLS"
    CurrentDb.Execute strSQL

    It failed miserably . . . the entire line of code turned red when I placed the WHERE clause as shown.

    I have little experience with SQL.

    Thanks

    Rick

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Placement of WHERE filter on UPDATE QUERY Solved

    This was successful in my case.

    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[ListOfficeID] = [TblCARETSOfcRostersAPR10].[OFFICEID] Set [tblCARETSData].[ListOfficeName] = [tblCARETSOfcRostersAPR10].[Officename] WHERE statement goes here!"
    CurrentDb.Execute strSQL

    Thanks.

    Rick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •