Filter records and update table column.

[Thompyt]

I am having issues with 2 almost like tables. I am trying to use the data in the first table [NBOI] to update a column in the second table [Data].[Networks2]. I am having difficulties in finding a way to make a 1-1 comparison in order to ptovide the correct net in the Networks column.

Can someone provide an assist?

NBOITest.zip

Thanks

[alansidman]

What are the common fields in both tables? What data from NBO1 do you wish to populate in the Data table. Need more information to even understand what you want to happen specifically. Tell us what you want as if we were standing in line for a coffee.

[Thompyt]

Common Fields
Data / NBOI

Title / Title
Para #/Para2
Para Desc / Description
Role / Role

I am attempting to fill in the Data.networks2 column from qryCombnet.ANet output to the correct owner in the Data.Role/NBOI.Role/Para. ANet derives from qryNBNet.Anet - Combining NBOI.Net1 and NBOI.Net2 based on NBOI.Net

Updated:NBOITest.zip

[alansidman]

Based upon the information you supplied, I created an update query, joining the fields that you have provided. Interesting, when I run the query

UPDATE Data LEFT JOIN qryCombNet ON (Data.Role = qryCombNet.Role) AND (Data.[Para Desc] = qryCombNet.[Para Desc]) AND (Data.[Para #] = qryCombNet.Para2) AND (Data.Title = qryCombNet.Title) SET Data.Networks2 = [qryCombNet].[ANet];

there are many empty rows in the field desscribed

I compared this by running this query to see if there was anything missing

SELECT Data.QID, Data.Title, Data.[Para #], Data.[Para Desc], Data.Role, Data.[Row Type], Data.Networks2, Data.Co, qryCombNet.ANet
FROM Data LEFT JOIN qryCombNet ON (Data.Title = qryCombNet.Title) AND (Data.[Para #] = qryCombNet.Para2) AND (Data.[Para Desc] = qryCombNet.[Para Desc]) AND (Data.Role = qryCombNet.Role);


and the same fields are empty.

I think you may be missing some data, it you expected these to be 100% populated.

[Thompyt]

Thanks, I did not expect to be 100% populated. The issue I am running into is where there are squads that are not assigned the correct net. If you notice the net assigned to PLT 2 is assigned to PLT 3 and PLT 3 has PLT 2. Emergency care SGT are all assigned to B when there is 1 assigned to A, B, and C. I am working something else, like temp changing the 1st to digits of the Para # in the Data table.

[alansidman]

Did you look at the second query and run it to see if it has the data you want. If yes, then you could run the update query.