Results 1 to 5 of 5
  1. #1
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18

    strSQL update

    I took this code from the Query SQL Its has three tables tblSurveyList , tblSurveylistsub and to update tbl_Export. Table tblSurveylistsub.SurveyListID_FK has multiple records that match tbl_surveylist.SurveyListID_PK . The records on Surveylistsub have different Fiscalyear dates which I am updating into the Export table.



    Attachment 31546Attachment 31547



    How would I run the same thing in a form design code to update the table tbl_export.
    strSQL = "UPDATE (tblsurveylist LEFT JOIN tbl_Export ON tblsurveylist.[SurveyListID_PK] = tbl_Export.[SurveyListID_PK]) LEFT JOIN tblSurveylistSub ON tblsurveylist.[SurveyListID_PK] = tblSurveylistSub.[SurveyListID_FK]" & _
    "SET tbl_Export.SurveyListID_PK = [tblsurveylist].[SurveyListID_PK], tbl_Export.SurveyName = [tblsurveylist].[SurveyName], tbl_Export.[FY 16/17] = [tblsurveylistsub].[FY], tbl_Export.[Kick off date1617] = [tblSurveylistSub].[KickOffDate], tbl_Export.[Design Start1617] = [tblSurveylistSub].[DesignStart], tbl_Export.[QDRC Testing Date1617] = [tblSurveylistSub].[QDRC_TestingDate], tbl_Export.[UAT Start1617] = [tblsurveylistsub].[UAT_Start], tbl_Export.[Collection Start1617] = [tblSurveylistSub].[CollectionStart]" & _
    "WHERE tblSurveylistSub.[Fiscalyear] = [FY 16/17];"
    DoCmd.RunSQL (strSQL)
    CurrentDb.Execute strSQL, dbFailOnError

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are you trying to UPDATE records already existing in tbl_export or are you really trying to add new records? Why do you need tbl_export? Why not just use query object for whatever you are trying to accomplish - which is export to Excel?

    Need space at end of each continued line so the SQL string doesn't run together.

    ...= tblSurveylistSub.[SurveyListID_FK] " & _

    ...= [tblSurveylistSub].[CollectionStart] " & _

    Don't use both RunSQL and Execute. I prefer Execute.

    WHERE clause doesn't make sense. Do you want to update only FY 16/17 records?

    Is SurveyName actually in Surveylistsub as shown or is that a lookup alias? You build lookups in table? I never do that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Battlescar is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    18
    Hi June7
    I will try execute. I will be updating all FiscalYears from FY 09/10 to FY 21/22. I can do it by changing the Query dates as shown from my update query but I was trying to update all the fiscal dates. I have attached the update query. Also part of the tbl_export and the export excel form where the data is going to go.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That is a very badly designed table / structure.
    You should never store data in your field names.
    Pretty much any time you see a field name with a number or date like Part No1, Part No 2 etc. the alarm bells should go off - you have a non-normalised database structure.

    All those dates and descriptions/actions should be in a separate table.
    Table_Action_Dates
    ActionID SurveyID ActionType ActionDate
    1 1001 Kick Off 01/01/2017
    2 1001 Design Start 15/01/2017
    3 1001 Testing Phase 1 23/02/2017
    4 1002 Final Test 21/03/2017
    5 1002 Kick Off 04/01/2017
    6 1002 Design Start 24/01/2017

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your original data appears to be properly normalized. Why do you need to pivot data and export to Excel? Instead of code to UPDATE tblExport, why not just use a CROSSTAB query?

    Why is Excel involved at all?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. strSQL Delete
    By HS_1 in forum Access
    Replies: 4
    Last Post: 01-04-2017, 04:30 PM
  2. How to add counter to StrSQL vba?
    By Jo.. in forum Queries
    Replies: 2
    Last Post: 09-09-2015, 08:09 AM
  3. VBA strSQL mismatch
    By SPW_12 in forum Programming
    Replies: 12
    Last Post: 07-31-2012, 07:47 PM
  4. Need to add 2nd criteria to strSQL
    By robsworld78 in forum Forms
    Replies: 8
    Last Post: 08-23-2011, 02:57 PM
  5. DLookup in strSQL for OpenRecordset
    By szucker1 in forum Programming
    Replies: 2
    Last Post: 07-30-2011, 06:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums