Need to update some blank fields

[Dave14867]

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

[moke123]

Are there related fields? What determines which mfrPartNumber goes to which record in Copy of Inventory Transactions ?

[Dave14867]

The ProductID is in the Copy of Inventory Transactions and is the same as the PartMain_Pk in the tblPartMain.

[Dave14867]

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.

[June7]

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;

[Dave14867]

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

[June7]

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.

[Dave14867]

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

[orange]

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.

[Dave14867]

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