Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Update Field based on Max Date

  1. #1
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20

    Update Field based on Max Date

    Hi I have a table with multiple Report Dates and I have another Column that is Blank that I have labeled "Current" I want to create an update Query to populate the Current column with "Yes" if the row is the Max Report Date and "No" if it is not.

    What criteria would I put in the Query Under Report Date to get this to work, it gives me an error saying can not have aggregate function in WHERE clause



    Click image for larger version. 

Name:	Query Setup.PNG 
Views:	17 
Size:	14.4 KB 
ID:	26984

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    Typically, you should never store anything which can be calculated. That is what queries are for.
    There is usually not any advantage in doing so, and actually doing so can undermine data integrity and the dynamic nature of the database.
    For instance, you would nee to blank out this value and re-run it every time you add new data. If you forget to do so, that field will be inaccurate.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    Yes that is correct this will be a saved query that I will run every week when I add new data to the table, that is why I need a calculated field created

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    But why does it need to be a calculated field as opposed to a a calculated field in a query, which would be dynamic (and no need to run anything manually)?
    As a general of thumb, storing calculated field in tables violates rules of normalization and threatens data integrity.
    Most of the time, it is not necessary at all to store them in a table. So can you tell us why you think they do need to be stored there (maybe there is a valid reason why it has to be that way)?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    It is not being stored in the table. I am creating an update query that will update the main table that is called "MK TBL MGMT Report Shell with Dates" weekly as part of a macro

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    I am creating an update query that will update the main table
    That is what I mean when I say "storing it in a table". You want to create a process that will store this calculation in a table field.

    What I am trying to tell you is that:
    - This is not a best practice
    - This is usually not advised
    - This is seldom necessary

    If you can explain why you think you need to store it in a field on table, we can determine if this really is the best way of doing it, or if there is another way.
    I guess what it really boils down to is what do you intend to do with this field? How/where will you be using it?
    There are sometimes reasons why it is better to do it that way, but it is usually pretty rare. We won't know until we know what you intend to use this field for.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    The data in my table will be exported after into Excel to update weekly management reporting and we have to track the history of the changes. If you know a better way to update this field please advise??

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    You can do export from Queries just as easily as you can from Tables.
    So there isn't a need to run Action queries each time, just create a query (or queries), and export the query.

    You could do it all in one nested query, or in a series of two queries.
    Basically, you would have an Aggregate query that identifies/returns your MAX values.
    Then, you would link this query back to your original table to add this column to indicate which records are the max values.
    You would just have to set this up once, then every time you add new data, you would just export this one (second) query.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  9. #9
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    I appreciate that you are an expert at Access and can do all these additional queries that sound great but I am only familiar with Action Queries so that is what I need to stick to right now and don't have the time in my new job to take Access training to do that. Which is why I have come on this forum to simply help me with my one Action Query to save me time and headaches So if there is a way to do it in the action query it would be fantastic if you would be so kind to share that.....

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    Action queries are actually much harder than basic select queries!

    I can help you do what you need to do, even come up with the code you need, if you just give us a few more details (that information would also be needed to do an Action Query anyway).
    This may actually be even easier than I first thought based on the answer to the following question.
    This Max Date you are looking for, is it for a particular group, or is it just one Max Date for everyone in the whole table?
    That is, are you just looking for the Maximum Date in the WHOLE column (not Grouping by any other fields), and want to know if a particular person has this value in their date field?

    If so, that can be done with a real simple query. Just do the following:
    1. Remove the Current field from your table, you do not need it.
    2. Create a new query adding all the fields you want to export, except for this "Current" field.
    3. Add the following calculated field to your query:
    Code:
    Current:IIf([Report Date]= DMax("Report Date","MK TBL MGMT Report Shells with Dates"),"Yes","No")
    4. Save your query

    Check it out, and it should return what you want.

    Now, all you have to do every time you import new data is export this saved query! No need to run any Action Queries first!
    So hopefully, that makes your life a little simpler and less headaches!
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  11. #11
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    Ok I tried that code and when I hit run it says " the expression you entered has an invalid . (dot) or ! operator or invalid parentheses" is there anything that should be changed in the code?

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    If the name of your table or field isn't exactly what is shown, you will need to edit that.
    Did you copy it "as-is", or edit it (if so, paste your edit)?
    You entered this expression in the "Field" row, and not in the "Criteria" row, right?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  13. #13
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    Ok no I did not edit the code and I pasted it in the Update To cell. I tried putting it in just the Field Cell as shown below but it tells me "Query must have at least one destination Field" so how would I tell it to update it to the "Current Field"??
    Click image for larger version. 

Name:	Query Setup.PNG 
Views:	10 
Size:	88.1 KB 
ID:	27007

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,958
    Go back to the directions I posted above. I added one word to help clarify it.
    1. Remove the Current field from your table, you do not need it.
    2. Create a new SELECT query adding all the fields you want to export, except for this "Current" field.
    3. Add the following calculated field to your query
    You are creating a Select Query here, not an Update Query.
    Once you create this query, there will be no need to run any queries anymore. Simply import your data, then export this Select query.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  15. #15
    janmack79 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    20
    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?

Page 1 of 2 12 LastLast
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