Capturing Latest Trasaction Data with an Update Query

[bhujanga]

Here's the situation:
I have a "Supplies" table that tracks supplies and various information relative to those supplies. Two of those pieces of information are the [LatestPrice] and [LatestVendor].

I have a historical "Procurement" table that is populated every time a purchase is made. The Items purchased must be items that are in the "Supplies" table. It is populated with the [date], [price] and [vendor] for that particular purchase.

Now, I want to be able to run a query that will find the most recent procurement (based on the purchase dates in the "Procurements" table) for each item in "Supplies" and put the price and vendor info from those procurement records into the [LatestPrice] and [LatestVendor] fields of the "Supplies" table.
I thought this was going to be a piece of cake, but I can't figure out how to make it work.

Note: I believe I have to do it this way rather than always accessing the information directly from the "Procurements" table, because occasionally we will want to override the procurement information by going into the "Supplies" table and entering it directly into those fields. This happens when we know the price has changed but aren't ready to buy more yet.

Thank you.

 

[Golom]

You can try

UPDATE Supplies As S INNER JOIN Procurement As P
       ON S.Supplycode = P.Supplycode

SET S.LatestPrice = P.Price, S.LatestVendor = P.Vendor

Where P.PurchaseDate = 
   (SELECT MAX(PurchaseDate) From Procurement As P1
    WHERE P1.Supplycode = S.Supplycode)

Note however that if you have gone into the Supplies table and over-ridden a price, this will wipe that out and substitute the corresponding information from the Procurement table. You may want to retain a date field in the Supplies table and modify the SQL so that the UPDATE statement will operate only on those records where the purchase date from Procurement is greater than the one in the Supplies table. You would of course, set the Supplies date to the current date when you over-ride the price field.

 

[TheAceMan1]

Howdy Golom . . .

Its been my understanding for quite some time that [blue]Domain Aggregate Functions[/blue] can't be used in an [blue]Update query[/blue]. In fact I've always failed when I tried in the past. For this thread I tried to work around a [blue]Sum[/blue] query using [blue]DMax & Group By[/blue], by joining the SQL as a table. All attempts failed.

Any input on this issue?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[bhujanga]

I was finally able to get a chance to try Golom's suggestion and it does seem to work. I don't fully understand how yet, but I'm working on it.

Thanks.

 

[Golom]

TheAceMan1

In general you're correct. An UPDATE statement, since it doesn't support GROUP BY, cannot usually support aggregate functions.

In this case however, the aggregate function is not in the UPDATE statement. It is in a coordinated sub-query about which the UPDATE part of the query knows nothing other than it returns a value that is used in the UPDATE's WHERE clause.