Results 1 to 5 of 5
  1. #1
    jlk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Location
    Australia
    Posts
    11

    Update to a table with code

    I have a database that I import data to each month. At the moment I manually update a table each month that identifies which months (in my case they are called SPs, this is a 4 week period instead of an actual month), I then use this table where true in queries to report on my data. I would like to automate this process of identifing the last 3 SPs instead of having to do it manually each month.

    The file name is SP06 (2013) Centre Database

    I have 2 columns that are updated. One of the columns is called “MostRecentSP” this is updated with the below code, which works perfectly.

    Sub SelectCurrentSP()

    strFullSPName = Trim(Left(CurrentProject.Name, 11))

    strSQL = "UPDATE tblSPList SET tblSPList.MostRecentSP = True " _
    & "WHERE (([tblSPList]![DisplaySP]='" & strFullSPName & "'));"

    DoCmd.RunSQL strSQL

    End Sub




    What I would like to do is update the “Last3SPs” column as well. I also have a column in this table with a running number ID. I was thinking there must be a way to update the "Last3SPs" when updating the “MostRecentSP”, and count back 3 SPs with these marked as true. I'm just not able to work out the code myself.

    I hope this makes sense? Really hard to put in writing what I’m trying to do.

    Attached is a screen shot of my table.

    Any suggestions would be greatly appreciated.
    Thanks


    Click image for larger version. 

Name:	SPtable.png 
Views:	13 
Size:	21.6 KB 
ID:	10607

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why do you need these two fields? It is possible for a query to retrieve the 'most recent' or 'last 3' SP records without those fields. The SQL needed for your 'last 3' update could just as well be used to simply retrieve those records. Review http://allenbrowne.com/subquery-01.html especially the example for TOP n records per group.

    What will you do when the DisplaySP number reaches 99?
    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
    jlk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Location
    Australia
    Posts
    11
    Hi June7

    I'm not currently using the column ID, it was previously suggested to me to use this as a way to identify each SP.

    All my queries run perfectly well when I update the yes/no box myself to true.

    Maybe i didn't explain myself. What my question is, how can I update the table to mark the last 3 SPs each month without doing this manually. Eg: next month with be SP07 (2013, so the code included in my first post actually updates this table to show that SP07 (2013) is marked as true. What i'm after is code to automatically update the Last 3 SPs where true, from SP04, SP05 & SP06 to SP05, SP06
    & SP07.

    SO I don't need to keep the ID column, if its not going to help me. I thought maybe I could use this in the code to allow it to count backwards to select the last 3 SPs.

    Please let me know if your confused?
    Thanks

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The ID column can often be useful even if it is not the primary key field. I repeat, the query to update the 'last 3' will involve sql syntax that could just simply be used to retrieve the records. Why the extra step of maintaining field? In fact, the SQL for what you want is even more complicated. I don't think an experienced database developer would do what you want, I wouldn't.

    Consider the TOP N query to simply select records - doesn't even require nested query:
    SELECT TOP 3 tblSPList.* FROM tblSPList ORDER BY tblSPList.ID DESC;

    If you must have these fields, first have to set field to false for all records before then updating the 'last 3' to true.

    The UPDATE queries:

    UPDATE tblSPList Set Last3SPs=False;

    UPDATE tblSPList INNER JOIN
    (SELECT TOP 3 DisplaySP FROM tblSPList ORDER BY ID DESC) AS Query1
    ON tblSPList.DisplaySP = Query1.DisplaySP SET tblSPList.MostRecentSP = True;
    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.

  5. #5
    jlk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Location
    Australia
    Posts
    11
    Ok , I understand now, thanks for your further explaination. I'll try this and i'll let you know. Thanks

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

Similar Threads

  1. Reusing VBA Code To Update A Table
    By Evilferret in forum Programming
    Replies: 3
    Last Post: 08-28-2012, 07:30 AM
  2. Writing Code for "After Update" Table Events
    By dipique in forum Programming
    Replies: 10
    Last Post: 07-09-2012, 08:11 AM
  3. VBA code to update field in table
    By vickan240sx in forum Access
    Replies: 1
    Last Post: 06-23-2012, 08:24 PM
  4. Replies: 1
    Last Post: 02-08-2012, 04:50 PM
  5. Replies: 3
    Last Post: 04-14-2010, 10:07 AM

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