Results 1 to 4 of 4
  1. #1
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48

    Update empty fields with max-values

    Hi All,



    I need to make an update query, but it seems it exceeds my knowledge.
    I convert the task into a pseudo-table for explanation

    The table (Customers) have the following fields:

    ID, CustId, Amount, Correction.

    There are fields, where the Correction-field has no value.
    What I need to do is to find the highest Correction from the given CustId-s and add it to to the correction-field, if it is empty.


    example
    1;CompanyA;100;(null)
    2;CompanyA;200;10;
    3;CompanyA;300;20

    record #1 should be populated with 20

    Thank you

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Bring the table in twice, using aliases.
    Update where Null from the Select Max where CustID same.

    I'd use the QBE GUI to do this, at least to start and get the basics.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thank you, do you mean, that I should use group by in the FROM table?

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    No, you want each individual record for the Update.
    The Max() will only bring in one record for the CustID

    As I mentioned I'd use the GUI to construct this, as my SQL from scratch is not great.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. In case of empty formular get all the values
    By sandordan in forum Queries
    Replies: 4
    Last Post: 03-06-2018, 03:52 AM
  2. Replies: 1
    Last Post: 10-29-2012, 08:15 AM
  3. Replies: 2
    Last Post: 04-23-2012, 10:13 PM
  4. Replies: 4
    Last Post: 11-20-2011, 01:08 PM
  5. Update Query on empty fields
    By bryan in forum Access
    Replies: 3
    Last Post: 01-18-2011, 11:36 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