Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28

Update Field based on Max Date

  1. #16
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    It would be a similar fashion, but I suspect that we will run into the same type of issues if you are unable to get this other thing to work.
    But there is a way around this, where I can give you the EXACT code you need. I just need you to do the following.

    - Create a new Select query in Access.
    - Add all the fields that you want to export to your Query view


    - Be sure to include "Report Date" field

    So there is nothing special going on here - just a simple Select query - no calculations, no criteria.
    Confirm that is working (by going to Datasheet View).
    Once you confirm that, change the query to SQL View, and copy and paste the SQL code here for me.
    Once I have this, I should be able to manipulate it to work for you.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  2. #17
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    16
    SELECT [MK TBL MGMT Report Shell with Dates].[Business Unit], [MK TBL MGMT Report Shell with Dates].[Full Name], [MK TBL MGMT Report Shell with Dates].[Opportunity Number], [MK TBL MGMT Report Shell with Dates].Name, [MK TBL MGMT Report Shell with Dates].[Sales Stage 2], [MK TBL MGMT Report Shell with Dates].[Report Date]
    FROM [MK TBL MGMT Report Shell with Dates]
    GROUP BY [MK TBL MGMT Report Shell with Dates].[Business Unit], [MK TBL MGMT Report Shell with Dates].[Full Name], [MK TBL MGMT Report Shell with Dates].[Opportunity Number], [MK TBL MGMT Report Shell with Dates].Name, [MK TBL MGMT Report Shell with Dates].[Sales Stage 2], [MK TBL MGMT Report Shell with Dates].[Report Date];

  3. #18
    Bulzie is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    528
    Jan what JoeM is telling you is the easiest way. The only difference in an Action query versus a Select query is the Select just returns the data, does not do anything with the data.

    So as JoeM said, in design of your update query, just click on the button at the top that says Select Query. Now you are in the Select query window which just returns data when you run it, does not update anything. All the fields from your table should already be in the columns I assume, if not add them. Then in a new column, past that code from JoeM like you have in your screen shot. Now run the query and see if the results are what you are expecting. If so then you can export this select query as needed.

  4. #19
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    OK I am sorry but that still does not work, change of plan.... can you tell me how to Query to find records that are NOT EQUAL to the Max Date?
    For some reason, that DMAX function is not behaving as it should. So, I took a little different approach, using a Subquery (which really just returns the Max Date which we are using as Criteria).

    Open a new query, change to SQL View, and paste this code in there and then change to Datasheet View to view the results.
    Code:
    SELECT DISTINCT[Business Unit],[Full Name],[Opportunity Number], [Name], [Sales Stage 2], [Report Date]
    FROM [MK TBL MGMT Report Shell with Dates]
    WHERE [Report Date] Not In 
    (SELECT Max([Report Date])
    FROM [MK TBL MGMT Report Shell with Dates]);
    Does this give you what you are looking for?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #20
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    16
    This is what happens when I do as instructed and hit run, here is a screen shot of the error


    Click image for larger version. 

Name:	Query Setup.jpg 
Views:	9 
Size:	131.1 KB 
ID:	27017

  6. #21
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    That is not the query I posted above. Note that the one I posted above does not have the word "Current" or "DMax" anywhere in it.

    Do exactly as I instructed. Open a brand new query (like you were going to build a NEW one), switch to SQL view, and copy and paste the SQL code that I posted above in post #19, and then change to Datasheet View.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #22
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    16
    Ok Joe yes that worked to return the records that it was supposed to in a Select Query, my one issue is that why can't I do it as an Update Query?? Reason being that I have 20 other update queries that I have to run to add data to other columns before I am ready to export the table. And I have another query that would feed off of the data that I populate in the Current column so I don't want to export it just yet....

  8. #23
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    Here is an Update Query that will set the value of the Current Field to "Yes" if the Report Date matches the Max Report Date:
    Code:
    UPDATE [MK TBL MGMT Report Shell with Dates] SET [Current] = "Yes"
    WHERE [Report Date] In 
    (SELECT Max([Report Date])
    FROM [MK TBL MGMT Report Shell with Dates]);
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  9. #24
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    16
    Ok perfect!! And what if I want a separate Update Query to populate the Current Field that DO NOT equal the Max Date to "No"?

  10. #25
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    You should be able to change the "Yes" to "No", and change the word "In" to "Not In".
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  11. #26
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    16
    Alright Perfect!!! Thank You so much Joe, I know that was a giant pain to figure out and I really really appreciate your patience I am sure I will be on this board more as I try to fix all these crazy complex formulas that this company has tried to do in Excel that I am trying to move to Access so that they don't have ridiculous 50MB files

    I hope you have a great weekend!!

  12. #27
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    16
    Joe I need further help on this to create another update Query which can be in separate Queries. So Query 1 : Find Report Dates that are from last week (Max Report Date >7)
    Query 1 : Find Report Dates that are from two weeks ago (Max Report Date >14)?? Can I modify the last formula to find those records??

  13. #28
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    Find Report Dates that are from last week (Max Report Date >7)
    So are you talking about returning a single date or multiple dates?
    "Max" implies one single date (the maximum).
    If you want all dates from the past 7 days, get rid of the Aggregate Max portion of the query and simple add Criteria to pull back the last 7 days (i.e. >=Date()-7)
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-25-2015, 03:17 PM
  2. Replies: 3
    Last Post: 01-09-2015, 04:48 PM
  3. Replies: 2
    Last Post: 05-22-2014, 05:11 PM
  4. Update Query based on existing date
    By axdxnco in forum Queries
    Replies: 1
    Last Post: 06-12-2013, 01:15 PM
  5. query to update a date based on another date
    By tkendell in forum Access
    Replies: 3
    Last Post: 06-29-2011, 08:32 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
  •  
Tech Forums: Microsoft Office Forums