Creating an update query

[nick243]

I am having trouble on how to best accomplish an update that needs to be made on incoming products. There is an image of my query in this zip file
Text file.zip

This is my select query of information from two different tables. I am trying to update any record with region 12, 12 and 00, but it can only be those combinations. I want to change the priority code to H for any of these combinations. My problem is that there are duplicate distro IDs because there are multiple regions possibly for each distro ids. For example if the distro ID has multiple regions with say region 2,4,10, I want to exclude those from my update to the priority code. Another example is if I have a only a singular Distro ID with the region 12 I want to update that to prioriy code H if it is not already H. Another example would be if a Distro ID has region 12 and has region 00 together, I want to update that Distro to H. My biggest problem is that there are multiple of the same distro ID that have different quantities for each region. I am looking for a way to work around this problem? Thank you for taking the time to read my thread. Any help would be apprciated.

Thanks,
Nick

[aytee111]

You say you are joining 2 tables but don't say which table you are updating or why the need to join them. Please provide further details on your tables or even better, attach a database with them.

[nick243]

I have 2 tables called DB Distros and DD Regions. I want to be able to update the DB Distro table in the field Priority to H if the region in the DD regions table for the Distro ID is 12 or 00 and 12. The two tables have a relationship through the Distro ID Field. I am including the tables and the query that I made in this database. The query is retailt0SH. The tables included are D-D Regions, and DB-Distos. Thank you

Text file2.zip

[aytee111]

Is this right?

Update all records with only one Region record and it is a 12.
Update all records with only two Region records, 12 and 00.
All others not updated.

[nick243]

Yes that is correct.

[aytee111]

First query - make table of record counts
2nd - update the record with a count of 1 and it is a 12
3rd - update the records with a count of 2 , 12 and 00

Make a copy of the tables before you run any update queries and check very carefully afterwards to make sure that the right records were updated!

[nick243]

Awesome! Thank you this works great. I will make sure o make a copy first. Thank you for your help.

Thanks,
Nick

[nick243]

OK, I did notice a possible problem. It is with the combination Update query that has to have both 12 and 00. I noticed that the criteria that was put in there was 12 or 00 and it would change the priority code if it found a 00 or a 12 but that is a problem because if you find a 00 it could also have like a seven code with the same distro. If this is the case we can't change the code at all. The count of two query helps but doesn't eliminate the possibility of the wrong combination. I believe the first update is correct. Thanks for your help. Is my suggestion on track?

[aytee111]

Definitely you are right. I made some changes.

[nick243]

I'm a little confused on how this one works. Can you explain the steps? Thanks

[aytee111]

First query adds 2 fields to the temp table: does it have a 12 and 00.
Second query updates it if it finds a 12
Third query updates it if it find a 00
If both of those are set to 1 and the count is 2 then it can only a 12 and 00 and should be updated.

[nick243]

I think it makes sense to me. I am having trouble following how it finds if its a 12 and updates. I believe though that this will do the trick. I notice that you also made a relationship between all the tables so that makes sense. I guess i won't know until I fully run the process.

[aytee111]

No I didn't touch your relationships, I leave that under your control. This isn't the real database, is it?

What don't you understand? It is only logic! The changes I made here are only when there are 2 records. Q 2a says that if there are two records for one DistID and one of them is a 12 then mark it (sets it to 1).Q 2b says that if there are 2 records and one of them is a 00 then mark it. This results in the temp table knowing when both of those regions exist, so only those where both are set will be updated in the main update query. Look at the data in the tables and you will see when those occur.

[nick243]

I ran through on the copy and tried to run all the update queries and there was an error in the operation. It said you must use and updatable query?

[aytee111]

Details! Which query? None of the ones I provided!