473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Get most recent record and use an attribute

294 256MB
Hello, all.

So, I have a Table that consists of a Date and a Value. When the date is the most recent, I'd like to get the NetAssetValue associated with that date, AND use it to multiply to a field from a different table in a query.

I have been unsuccessful in using DMax or Sort Descending (to get the most recent date), and thus I am stuck asking this question.

award.jpg

I, essentially, would like to replace the NAV_Date with the most recent date, thus, it would return the appropriate NetAssetValue. As seen in the image, when the NAV_Date is correct (9/30/2013) it used the correct NetAssetValue ($2,500.00).

Anyone have any ideas?

Thanks

Nov 15 '13 #1

✓ answered by Seth Schrock

The Date() function returns the current date. Unless you have a time stamp or a serial number that can tell you which one was entered last there is no other sure way to know which one is the most recent. In VBA there is a LastModified property to the recordset object, but unless you can guaranty that no one can modify existing records, then you can't be sure that it will return the most recent record.

9 1442
Seth Schrock
2,965 Expert 2GB
You can run a query that gets the closest date and then do a dlookup to get the NetAssetValue. Your query would be something like
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM AwardsByYearRptQry
  2. WHERE DateDiff("d", NAV_Date, Date()) = (
  3.    SELECT MIN(DateDiff("d", NAV_Date, Date())) 
  4.    FROM AwardsByYearRptQry 
  5. )
In this case, this query would return the top four records. You would then just have to figure out which of the four you want returned in your DLookup.
Nov 15 '13 #2
mcupito
294 256MB
Thanks for your reply. What does that Date function do? Also, it returned some records with the most recent NAV_Date, however I am still unsure of how to correlate that data to the original query to use the most recent NetAssetValue?
Nov 15 '13 #3
Seth Schrock
2,965 Expert 2GB
The Date() function returns the current date. Unless you have a time stamp or a serial number that can tell you which one was entered last there is no other sure way to know which one is the most recent. In VBA there is a LastModified property to the recordset object, but unless you can guaranty that no one can modify existing records, then you can't be sure that it will return the most recent record.
Nov 15 '13 #4
mcupito
294 256MB
So I think I found a solution. I created another query that merely gets Max NAV_Date and Last NetAssetValue.. I used that query in the main query and used only the NetAssetValue from my "RecentNAV" query and it seems to be working..

Thanks all.
Nov 15 '13 #5
Seth Schrock
2,965 Expert 2GB
Glad you got it to work.
Nov 15 '13 #6
mcupito
294 256MB
Thanks for your help, Seth.
Nov 15 '13 #7
NeoPa
32,556 Expert Mod 16PB
NB. If you want the current date with the current time element included also, use Now() instead of Date().
Nov 16 '13 #8
Seth Schrock
2,965 Expert 2GB
The Now() function wouldn't help if the field didn't have a time element in it though, right?
Nov 16 '13 #9
NeoPa
32,556 Expert Mod 16PB
Not really. No.

If both the setting and the checking are done with Now() instead of Date() though, it all works more as required.
Nov 16 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Nova's Taylor | last post by:
Hi folks, I am a newbie to Python and am hoping that someone can get me started on a log parser that I am trying to write. The log is an ASCII file that contains a process identifier (PID),...
3
by: Tim Morrison | last post by:
MSSQL2000 I have a table that contains customer transactions CustomerID Transaction TransactionDate .... I need to select the most recent record that matches a specific CustomerID.
2
by: Shaiguy | last post by:
I have a table containing the following fields: ProjectUpdateID (PrimaryKey) ProjectID UpdateDate I would like to create a Query in Ms Access 2000 which will return me the most recent 2...
4
by: doublej0 | last post by:
I have the following issue when trying to write a SQL statement to accomplish my goal. I have done a lot of research, but have been unsuccessful in creating the query. I have two tables, one with...
1
by: evenprimes | last post by:
Here's the table setup: table1: -key -name table2: -key -modification_date -phone_number
1
by: mlwils2 | last post by:
I'm working with a report that uses three views. There are duplicate records because the 'priority' which comes from one view has changed and SELECT DISTINCT sees it as a separate record. The users...
1
by: Jonathan | last post by:
I have the following query which is retrieving a set of data it is almost what I want but I can not manage to get the result I desire. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output...
6
by: pradkumar | last post by:
Hi All I am having the a table(1 million) with data as follows: Input: Col1 Col2 Col3 1 1895 4/7/2007 2 1895 4/7/2007 3 1895 3/5/2005 4 6600 3/3/2007
7
by: brewer95 | last post by:
I use a form to enter data into a table. The user then has the opportunity to recall the data in the form based on a search to review and update the info. For example, on my menu there is a combo...
1
by: jpgoeth | last post by:
What I'm trying to do is select the most recent record for each customer who created an account between 2004-01-01 and 2007-01-01. I'm using MSQuery. I have this so far: SELECT OrderOrAdjust,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.