Update Value Based on Most Recent Record

[mcupito]

I am creating payouts, that can increase or decrease in value based on their NetAssetValue, which is determined each quarter.

I have a Payroll Request table and NAV table. I am trying to update each record in the payroll request table with the most recently entered NetAssetValue. Now, I have a query that gets just that, however it appears it isn't compatible with an Update query. (I tried)

Anyone have any other ideas on how to do this?

Again, I am trying to get the most recent record from one table and update it to another. (Only using that 1 most recent value)

[zmbd]

WHY?
If the value is stored once, then you do not need to normally store it a second time - this breaks the rules of normalization (> Database Normalization and Table Structures. )

The query returns the correct value for each quarter yes? Then use that value directly in your calculations, reports, or whatever else you need.

Another thing, you haven't provided the SQL behind your query. Without that we can't begin to tell you why it's not suitable for an update query. Open in design view, change to SQL view and copy, come here, click on the [CODE/] and paste the SQL between the text.

You should be able to create a simple select query against the query that returns your value. Once done, use this as your update action query.

If you wish to use vba then you can open a recordset against your stored query and then pull that value out and use it in a standard update query that way too.

[mcupito]

Thanks for the reply, zmbd. It's weird. The NAV Date is the identifying field to determine the Net Asset Value (because they are simply updated quarterly) However, when I run through each query 1 by 1, it puts a random date of 2/27/1906 in the NAV Date field. Somewhat odd?

[zmbd]

... wierd... we will really need the SQL of the query to figure out what is happening.

[mcupito]

I figured it out. The query didn't have the appropriate joins. Thanks for your help, zmbd.