Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    30

    Unanswered: Microsoft SQL in MS Access - update query with max results

    I have a bit of code for an update query in Access.
    There are several fields of data for the reference number in a table and there can be also be multiple entries for a reference number if the fields are changed at a later date.

    eg.
    Ref Number|data item 1|data item 2 etc| entry date
    1584541228 | abc | abc1 | abc2 | 01/01/2008
    1584541228 | abc | abc2 | abc1 | 19/11/2009
    4548645454 | gdk | abc2 | anc3 | 01/11/2009

    Each month we get a new set of reference numbers which are loaded into another table. What I need to do is check the new list of reference numbers against the old one that contains all the data, and if theres a match, insert the latest data into the new table.
    eg.
    1584541228 | | | |
    4548645454 | | | |
    For ref 1584541228 I would need to insert the info from the archive table into the live table for just the entry date 19/11/2009, and 4548645454 all the data as theres only one record for that one.

    The code ive got so far looks like this:

    UPDATE no_aa_archive INNER JOIN no_aa_dayfile

    ON no_aa_archive.REF_NO = no_aa_dayfile.REF_NUM

    SET
    no_aa_dayfile.[Next Action Date] = no_aa_archive.[Next Action Date],
    no_aa_dayfile.[Last Action Date] = no_aa_archive.[Last Action Date],
    no_aa_dayfile.[Resolution Date] = no_aa_archive.[Resolution Date],
    no_aa_dayfile.[Action Taken] = no_aa_archive.[Action Taken],
    no_aa_dayfile.Comments = no_aa_archive.Comments,
    no_aa_dayfile.Name = no_aa_archive.Name,
    no_aa_dayfile.[Date Taken On] = no_aa_archive.[Date Taken On],
    no_aa_dayfile.[Report Month] = no_aa_archive.[Report Month]

    WHERE (((no_aa_archive.[Date Taken On]) Is Not Null));

    The problem im getting is in the archive table if there is more than one entry (i.e the example I gave above), I need to update with just the latest 'date taken on' record.

    Ive read that MS Access SQL cant update tables that contain max and mins in the code, so im assuming that subqueries are out the question. However I have read that it is possible to use Dmin and Dmax in the code, and to be honest I know nothing about them at all. If I have to redo the query in VBA im really stuck!

    Help would be really appreciated.

    Thanks

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    How about this: let's create two views.

    Code:
    CREATE VIEW LatestRefs AS
    SELECT RefNumber, Max(EntryDate) as EntryDate
    FROM no_aa_archive
    GROUP BY RefNumber
    
    CREATE VIEW LatestArchiveData AS
    SELECT a.RefNumber, a.Data1, a.Data2 ..., a.EntryDate
    FROM no_aa_archive a INNER JOIN LatestRefs r
    ON a.RefNumber = r.RefNumber AND a.EntryDate = r.EntryDate
    From what I can gather, you're copying the data from the archive to the daily file. So that's just going to be:

    Code:
    UPDATE LatestArchiveData lad INNER JOIN no_aa_dayfile nad
    ON lad.REF_NO = nad.REF_NUM 
    
    SET 
    nad.[Next Action Date] = lad.[Next Action Date],
    nad.[Last Action Date] = lad.[Last Action Date],
    ... blah blah blah...
    There's no need for the WHERE clause because INNER JOIN won't select non-matching records.

    If Access complains about updating over the join, you could always just create a new table instead. In that case, use RIGHT JOIN instead of INNER JOIN.

  3. #3
    Join Date
    May 2009
    Posts
    30
    Thanks for the code. I put it in, and im now getting an error message stating "Syntax error in create table statement", the code is as follows:

    Sub test()
    Dim strSQL As String
    strSQL = "CREATE VIEW LatestRefs AS" & _
    "SELECT REF_NO, Max([Report Month]) as EntryDate" & _
    "FROM no_aa_archive" & _
    "WHERE [Date Taken On] Is Not Null" & _
    "GROUP BY REF_NO;" & _
    "CREATE VIEW LatestArchiveData AS" & _
    "SELECT a.*" & _
    "FROM no_aa_archive a INNER JOIN LatestRefs r" & _
    "ON a.REF_NO = r.REF_NO" & _
    "AND a.[Report Month] = r.EntryDate" & _
    "AND a. [Date Taken On] is not null;" & _
    "UPDATE LatestArchiveData lad INNER JOIN no_aa_dayfile nad" & _
    "ON lad.REF_NO = nad.REF_NO" & _
    "SET" & _
    "nad.[Next Action Date] = lad.[Next Action Date]," & _
    "nad.[Last Action Date] = lad.[Last Action Date]," & _
    "nad.[Next Action Date] = lad.[Next Action Date]," & _
    "nad.[Last Action Date] = lad.[Last Action Date]," & _
    "nad.[Resolution Date] = lad.[Resolution Date]," & _
    "nad.[Action Taken] = lad.[Action Taken]," & _
    "nad.Comments = lad.Comments," & _
    "nad.Name = lad.Name," & _
    "nad.[Date Taken On] = lad.[Date Taken On]," & _
    "nad.[Report Month] = lad.[Report Month]"
    DoCmd****nSQL strSQL
    End Sub

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you give us just the resultant value of strSQL please?
    George
    Home | Blog

  5. #5
    Join Date
    May 2009
    Posts
    30
    If you mean the result of just the SQL, tried running it as that and got the same/similar error...."error in create table"

Posting Permissions

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