Update Query that updates to minimum value

[linvall]

Hello,



I'm trying to create an update query that updates values in one table with values of another table. With my current query, it is updating the correct column, but is updating the value based on the first filled cell. I want it to update to the minimum cell ( usually a blank cell).

Example: Table 1 Col2 updating Table 2 Col2

BEFORE UPDATE

TABLE 1
COL1 COL2
1
1 X
2 Y
3 Z

TABLE 2
COL1 COL2
1 U
2 S
3 T

AFTER UPDATE (WHAT DATABASE DOES)

TABLE 1
COL1 COL2
1
1 X
2 Y
3 Z

TABLE 2
COL1 COL2
1 X
2 Y
3 Z

AFTER UPDATE (WHAT I WANT IT TO DO)

TABLE 1
COL1 COL2
1
1 X
2 Y
3 Z

TABLE 2
COL1 COL2
1
2 Y
3 Z

Here's the link to the current set-up of my update query: https://ibb.co/cYA6Up

Thanks

[Micron]

You want table 2 to update to the minimum "cell" (tables don't have cells, they have fields) but the minimum value of what field? 1 or 2? Your example of the desired result leaves me to wonder what happened to the X. Maybe this is pseudo data and we'd be better able to figure something out if you posted something closer to the real thing?

Also, it would be better if you uploaded images directly into your post instead of linking to them. When the linked image is no longer available, the post will lose information.

EDIT - also seeing that you have 3 linked fields, but are only referring to 2 in your post. With your current query design, you can only update where all 3 linked fields are equal, so is that OK? The Y and Z values from 1 table will never equal U S T in the other, but that's the constraint you've applied. The only ones that might would be the Nulls. Again, that's why real data would be more useful. Easiest IMHO is to copy/paste an Excel range into your post.

[linvall]

The data is at work so I will post it tomorrow. And yes, from the picture, i only ever want the hold_released_by field to be updated, the other 3 are linked for uniqueness, as the 3 other variables can change. Just need it to find a record with 3 of the other variables and then update the value to the minimum value of the fourth column in the first table of the matching record.

[linvall]

excel.zip
Here is the excel with some data.

[Micron]

Sorry, I'm lost. Both versions of Table1 and Table2 in the file look exactly the same. You want to update Holds.HoldReleasedBy to the value of AllHolds.HoldReleasedBy which I guess is 1 and 2 respectively. However, I see no difference between 1's start and 1's desired updated result. You could try adding Null in the query criteria field for HoldReleasedBy since that is really the only difference between them. You are probably getting an update of Null on the first pass (1st record from 2 where joined fields are equal) then an update from the second pass where the problem field contains a name. When updating tblA from tblB where B contains multiple records, you have to filter the many side down to only one possible value.

Aside from all that, if these tables always exist in the same db you might have a normalization issue because there's a lot of repeated data.

[linvall]

These tables are getting values from a huge ODBC. They look the same because Table 2 is linked directly to the ODBC while Table 1 is just a copy of Table 2. When a work order comes in, it has any tasked linked to it, with many holds linked to each task. That's why there's duplicates in the work order and task column. What I need it to is update when the last hold on a task is released, so that I know that task can be sent to another department so work can carry out on that task. I can tell a task has no hold when all the holds have been released (the blank is replaced by a name in hold_released_by). When all the names are filled under that task, I automatically know that the task can be sent out. All I need the query to do is update hold_released_by) with the minimum value (a null value until the hold has been released, than any name under that task will do) so that when the task still hasn't been released from holds, it will show up blank, which means work cannot proceed. When the last blank is filled, the minimum will simply be just a name. When I see a name, I know that the task has no holds on it (as there are no more blanks) and work can proceed.

[Micron]

I may not be able to get back to this until sometime late tomorrow as I have out of country company. Explanation seems good but I'll need time to study it. Thought I saw a flaw based on the explanation and excel file, but maybe not. What might help is if you zip and post a sample db with relevant data and a sample query as you see it, regardless if that query works as desired. Might be nice to have something to play with.

[linvall]

Ya, I shouldn't be posting data because of the companies policies but I will try and make a little mock up of the data and post it on here some time in the afternoon.

[Micron]

Pseudo data is OK. When you post back, please clarify what this means in spreadsheet:

<-- I want the HOLD_RELEASED_BY in this record (always the minimum value) to update the HOLD_RELEASED_BY value in Table one

If the data is text (e.g. "SHESTOWM" or "NUTTALS") which one would ever be the minimum?

[linvall]

To me it doesn't matter, I would think that the minimum would just go in alphabetical order. Maybe want I'm really for is to sort everything in ascending order so the first thing that will be pulled is the null or blank value. Another thing, how big can the files be when attaching a file, been trying to post the database (14mb) but it wont finish the upload process.

[Micron]

Try compact/repair to get the size down. Regardless, you have to zip it before trying to upload. Can't recall what the max is, but it's no where near 14 Mb.

Sorry, still don't understand. What does sorting have to do with wanting to update tableA from tableB table using the minimum value from a field in tableB? Are you using the correct term? I'm trying to find out what constitutes "minimum". Do you mean earliest by date? First/last by some kind of sort order? Least based on a Count of the values in some field? Minimum/maximum is usually number based and I see no evidence of numbers in your data. Also, you cannot rely on the datasheet view of table records always reflecting the order they were added to the table. So if you have some sort of order that relates to your use of the word 'minimum' it is best achieved by basing forms/reports on queries instead of tables. Suggest you keep that in mind when developing.

[linvall]

The order I'm looking for is something like this:
1. null or blank fields
2.names

It doesn't matter about the order of the names, as long as the update is using the empty spaces to update the tables first, and then when there are no more empty spaces, it uses any name, doesn't really matter to me. When I see the name instead of a blank space, it means there are no more holds on the task and it can be sent out for work to proceed on it, so I need to see the blank or null space until the hold has been released.

[linvall]

Database21 (4).zip
Sample database. Added a copy of the tables so you always can replace the copy if things don't go the way you expected them to go.

[Micron]

OK, got the db; if I have q's about the db, I'll pm you rather than clutter this thread with questions and responses.

[Micron]

what I did:
- created LOCAL and ODBC tables (from Holds & All Holds respectively)
- removed Allow Zero Length property where found
- updated these fields via query (not in db) to convert zero length ("") to Null
- created qrySelectUpdates; returns wo# and task# with LAST of code and LAST of released by
- created qryMTtblUpdateTemp (makes tblUpdateTemp using qrySelectUpdates records)
- ran to create tblUpdateTemp
- created composite index on temp; wo# and task# (not set to Primary, so this isn't obvious). More on that when you're ready.
- renamed query to qryAppendTemp; in future, appends only new records to temp. Those that violate index don't get added. Access gives prompt re: records it can't append. More on that later too.
- created qryUpdateLocal; to update LOCAL from temp table.
- ran qryUpdateLocal, updated status and 'hold by' for each record. I have no idea whether or not LAST of either of these fields is OK.

Notes:
- it isn't the temp table that's temporary, it's the records. None of this is perfect even if you can't fix your data (assuming it needs to be). If you maintain this procedure, you may want to introduce a delete from temp so that new wo records don't get added forever. You'd then have the option to remove the temp index since you'd always be starting with an empty table, assuming the append query never creates duplicate wo/task combinations. IMHO, the LOCAL table should have this index. The process might be
- delete * from temp using a delete query
- run qryAppendTempt to append what's current to temp
- run qryUpdateLocal to update "hold code" and "released by" to local

- Also, are wo number and task in ODBC are really text?

How you run this would be up to you. Running action queries via code or macros will present prompt messages. Turning off/on warnings would fix that, but is a bit risky and should involved proper error handling lest your warnings are turned off for an entire session. Execute method of Database object would be better since it won't message you unless you ask it to AND there is a failure. Execute can also be run on sql statements if you want to dump the query objects.

The process might be less clunky if you used code to create a recordset from qryAppendTemp (first changing it to a SELECT query) and use it to update a recordset of table LOCAL. Note that I didn't say easier as it would need a fair bit of code. It would simply eliminate the temp table and having to append to it in order to update LOCAL with those records, but at a design/labor cost which would make updates harder.

See if the attached helps.

Database21b.zip