Results 1 to 10 of 10

Need to update some blank fields

  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    250

    Need to update some blank fields

    Hello all,



    I still haven't got a good grasp on queries. I want to update some empty fields in a table based on the value of another field in the same table. In the attached file there are 2 tables, Copy of Inventory Transactions and tblPartMain. Essentially I want to find every row where "PartNum" is empty in "Copy of Inventory Transactions" and fill it with "MfrPartNumber" from "tblPartMain".

    I started a query (query1) but I don't know how, using the builder, to get the info from "tblPartMain" into the update field.

    Am I missing something in the query builder because no expression categories are aviable to select?

    Thanks

    Dave

    TransTable.zip

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    308
    Are there related fields? What determines which mfrPartNumber goes to which record in Copy of Inventory Transactions ?

  3. #3
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    250
    The ProductID is in the Copy of Inventory Transactions and is the same as the PartMain_Pk in the tblPartMain.

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    250
    I can get the query to identify all of the rows where the "PartNum" is blank but I cannot figure out how to update the blank field with the MfrPartNumber from the tblPartMain. I need to find a way to use the ProductID then fill in the Blank field with the corresponding MfrPartNumber.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    First, might need to fix some data.

    TransactionID 27059 and 27060 - there is no ProductID.

    TransactionID 2130, 2136, 15019 - there is no TransactionDate

    PartMainID_PK 1308 - there is no MfrPartNumber

    And some records have empty string instead of Null in PartNum which means the filter criteria will fail. Need to handle this in query. Try:

    UPDATE [Copy Of Inventory Transactions]
    LEFT JOIN tblPartMain
    ON [Copy Of Inventory Transactions].ProductID = tblPartMain.PartMainID_PK
    SET [Copy Of Inventory Transactions].PartNum = [tblPartMain]![MfrPartNumber]
    WHERE Nz([Copy Of Inventory Transactions].PartNum,"")="";

    Alternatively, don't use WHERE clause and let every record update. This assumes MfrPartNum associated with each PartMainID_PK has not changed so PartNum value will not change in the UPDATE if it already has valid data.

    However, there really should be no reason to have part number in both tables. Use query joining tables to retrieve related data. This is a major principle of relational database - to not duplicate data. This JOIN is already used in the UPDATE, so instead of UPDATE just do a SELECT query, much simpler than trying to figure out UPDATE action.

    SELECT [Copy Of Inventory Transactions].*, tblPartMain.*
    LEFT JOIN tblPartMain
    ON [Copy Of Inventory Transactions].ProductID = tblPartMain.PartMainID_PK;





    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.

  6. #6
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    250
    June7,

    I have corrected the data in the fields that you identified. I am curious though how you can tell that "And some records have empty string instead of Null in PartNum which means the filter criteria will fail".

    I agree that having "PartNum" in the table goes against "normalization" but it makes it easier for me to see that what got written to the table is correct. This is the only table that both the ID and PartNum appear. (This is just 1 table of many in the whole database). This was one of my first databases and I have learned the error of my ways thanks to folks like you willing to teach others. I will always be learning.

    Where should I put the Statement

    UPDATE [Copy Of Inventory Transactions]
    LEFT JOIN tblPartMain
    ON [Copy Of Inventory Transactions].ProductID = tblPartMain.PartMainID_PK
    SET [Copy Of Inventory Transactions].PartNum = [tblPartMain]![MfrPartNumber]
    WHERE Nz([Copy Of Inventory Transactions].PartNum,"")="";

    Thanks

    Dave

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    I found the empty strings by setting criteria in query.

    SELECT * FROM [Copy Of Inventory Transactions] WHERE PartNum Is Null;

    returns different records than

    SELECT * FROM [Copy Of Inventory Transactions] WHERE PartNum = "";

    In both cases the field looks blank but the reason for blank is different. Null and empty string are not same thing. The reason I even did the empty string test was because the Is Null criteria UPDATE left a bunch of records not updated. When I ran update with empty string criteria, records updated.

    Part of why this is confusing is that Access ribbon and right click filter/sort sees Null and empty string same (Equals Blank) but VBA and SQL do not. I NEVER allow empty strings in text fields.

    That is SQL statement as would be seen in SQL View of query builder. Can copy/paste (then can switch to Design view to see how it is presented there) and run query with Run button on ribbon or Open query and it will execute.
    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.

  8. #8
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    250
    June7,

    Yes I know that Null and an empty string are different but I hadn't checked for that, I made an assumption that I shouldn't have.

    I was wondering if copying and pasteing into the SQL view of the builder would work but I didn't want to try it until I asked first.

    Thank You

    Dave

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,102
    Dave,

    I recommend you watch some videos on queries and/or review some info at w3schools
    and/or Google youtube steve bishop ms access queries.

    Set up some practice table(s) with a set of things to ask and set up and test/try/experience some queries.

    Update queries can be quite unforgiving. Make sure you understand clearly the scope of the update.
    Scope criteria usually means a WHERE clause of some sort to constrain the UPDATE to a "set of qualifying records".

    A common practice is to do a select query first to ensure you have clearly defined the scope - the records to be updated.
    When you have that confirmed, then adjust the query from SELECT to UPDATE. You may want to make a copy of the table before doing any updates so that you can get back the original --just in case things go awry.

    Good luck.

  10. #10
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    250
    orange,

    I had set up the query as a Select at first as you suggested to see what was going to be selected and I was working with a copy of the Table. I do need to get alot better at queries though.

    Thanks for the tips

    Dave

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

Similar Threads

  1. Replies: 1
    Last Post: 01-31-2018, 04:06 PM
  2. Update blank fields in a table to "BLANK"
    By Phil Hoop in forum Queries
    Replies: 2
    Last Post: 10-21-2017, 07:42 PM
  3. Replies: 1
    Last Post: 06-11-2016, 05:46 AM
  4. Replies: 3
    Last Post: 04-24-2016, 07:24 AM
  5. Calculated fields going blank after update
    By Monterey_Manzer in forum Forms
    Replies: 3
    Last Post: 10-08-2012, 03:21 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