Update Query Help!!!!!

[coffeeman]

I have a sample of my database attached.

In the sample, I have a Receiving table that gets updated when a material (Boxes/Bags) is received. I tried to create an update query so that I could run it on open of a form.

This update query will run so that when something is received or "Completed", the earliest date of a particular type of box will be selected as "YES" for CurrentLotCode.

I had to run 2 queries to get the correct info to be based off the MinofDate Received. (this may be wrong!!!) The results for these queries come out with 37 entries (which is correct) that are not completed lot codes and the earliest available for each type according to date received.

I then created the update query and instead of updating 37 items, it is updating 50! It will select the oldest Lot Code that hasnt been completed for each type, but will also randomly select others of the same type that are newer (received after the one that should be correct)

Can someone take a look at it for me? You may want to go to table first. It is setup how the selection of current lot code should be. When you run the update query, go back to the table and see that multiple selections have been made for certain box types (not all) at random.

THANKS!!!

 

[coffeeman]

If you cannot open that one, here is a zipped version of it. PLEASE HELP if you can!

 

[coffeeman]

In case anyone was wondering I found this website that explains why you cannot base an update query on an aggregate query.

http://support.microsoft.com/kb/116142

I had to make a temp table for the totals query to feed into and then run an update query based off the temp table.

This is pretty annoying. I have to run the totals query first to make sure it is correct and then run the update query.

Does anyone know of a better way?

 

[coffeeman]

I actually got it to work pretty well! Here is the new queries and how it runs now.

You can make changes in the table to see if it works the way you want it.

Just figured someone could benefit from this and not deal with the headaches I have had for a couple of days.