DMax()? (1 Viewer)

[mdcory]

I need to get the most recent data from a coulmn that is not null. The table is dbo_SouthVariable1 and the coulmn is AIM_N7089 and the date column is Rec_Date. I tried using DMax but not getting it to work for me.
I hope you can understand this, I'm not very good at explaining what I mean.

Thanks...

 

[krunalprajapati]

what do u mean by recent data ?
means u want the last data entered.
then make ur date fileds datatype to date/time. and give it default value =Date() and the format property to that field as "dd/mm/yyyy hh:mm:ss"

then u can find out the recent data as I mean (the last data u saved), by using the max(ur date field name).

 

[mdcory]

Sorry, let me see if I can explain this a little better. There is data entered everyday in some fields but not the in the field that I need to find the most recent. It might have data 3 days in a row and not have any for the next 2 days, but there is a new record made everyday so for today that field might be null, and I need to find the most recent record for that field that is not null. Is that any easier, I think I confused myself.
Thanks

 

[Ashfaque]

Hi,
Still confused..

Do you need to get the latest data based on date column (Rec_Date) or on AIM_N7089 ?

What I understood from your first thread is you need the latest recordset based on your one of the above column.

Example:

Let us say your column AIM_N7089 contains part_number as follows:

120
120-A
120-B
329
329-A

Do you need your query result to be as follows ?

120-B (Latest part from series 120)
329-A (Latest part from series 329) and so on..

If not this, attach your db zip with minimum records if possible.

Regards,
Ashfaque

 

[mdcory]

Thanks,

this is an example of what I have, ignore the dashes used for formatting.

Rec_Date----- AIM_N7089----- AIM_N7090----- AIM_N7091
12-18-04----- ---------------- 3.4------------ 4.4
12-17-04----- 2.6 ------------ 3.5------------ 3.4
12-16-04----- 2.3 ------------ 3.4-------------4.0

So I would need to get the data (2.6) from AIM_N7089 since it is the most recent data in that column.

thanks again

 

[Ashfaque]

mdcory,

I hope attached zip file will help.

Regards,
Ashfaque

 

[mdcory]

nope sorry for the confusion. I need the data from the AIM_N7089 column with the most recent date in the date column that has data in the AIM_N7089 column. In other words if today has data in the AIM_N7089 column I need that data, if today and friday do not have data in the AIM_N7089 column but thursday does I need that data since that would be the most recent. I feel for all of you reading this because I confuse myself the more I try to explain this. I also need to do this in a query so I can use it in a caluclation.

 

[Jon K]

If the table is not large, you can use a subquery with a Where Clause:-

Query1:
SELECT [Rec_Dated], [AIM_N7089]
FROM dbo_SouthVariable1
WHERE [Rec_Dated] = (SELECT Max([Rec_Dated]) FROM dbo_SouthVariable1 WHERE [AIM_N7089] Is Not Null);


If the table is large, you can index the [Rec_Dated] field in the table, and use a two-query approach like this (run the second query):-

Query2a:
SELECT [Rec_Dated], [AIM_N7089]
FROM dbo_SouthVariable1
WHERE [Rec_Dated] BETWEEN (SELECT Max([Rec_Dated]) FROM dbo_SouthVariable1) -3 AND (SELECT Max([Rec_Dated]) FROM dbo_SouthVariable1);

Query2b:
SELECT *
FROM Query2a
WHERE [Rec_Dated] = (SELECT Max([Rec_Dated]) FROM Query2a WHERE [AIM_N7089] Is Not Null);


The first query retrieves the records for the last four days (to allow for possible weekend non-entries) from the table, so the second query needs to process at most four records no matter how large the table is.

Though two queries are used, this approach is more efficient than Query1 and runs very fast when [Rec_Dated] is indexed.
.

 

[Pat Hartman]

krunalprajapati,

and give it default value =Date() and the format property to that field as "dd/mm/yyyy hh:mm:ss"

Date() does not include time of day. The correct function is Now().

mdcory,
This is best done in Access with two queries because Jet does not properly optimize subqueries. The first query obtains the date of the record that contains the information you want and the second query obtains the data by joining to the first query. If the selection from the first query is not unique, the final query will return more than one row.

Query1:
Select Max(Rec_Date) As MaxDate
From YourTable
Where AIM_N7089 Is Not Null;
Query2:
Select * From YourTable InnerJoin Query1 On YourTable.Rec_Date = Query1.MaxDate;

 

[DLB]

After reading this thread, I was interested to find out how Access's help files say about subqueries and found this:-

Ways to optimize query performance

Avoid calculated fields in subqueries. ........

Avoid using domain aggregate functions, such as the DLookup function to access data from a table that's not in the query. Domain aggregate functions are specific to Microsoft Access, which means that the Jet database engine can't optimize queries that use them. Instead, add to the query the table that the function was accessing, or create a subquery.

So it seems, in some situations, we can use subqueries to optimize query performance. Is this deduction correct...?

I decided to compare Jon K's two-query approach (which contains subqueries in both queries) with Pat's two queries (which are free from subqueries). I tested them against a table using 60,000 records. And these were what I found:-

Jon's queries took an average of 0.09 seconds to return the most recent AIM_N7089.
Pat's queries took an average of 0.48 seconds to return the same AIM_N7089.

So, based on these results, I think we can sometimes use subqueries to optimize query performance.


I have attached the database that I used for my testing. In order to meet the size limit here, I have to reduce the number of records to 12,000. And I can still see some difference in performance. (If you are interested in testing them on a fast computer, you may need to increase the number of records in the table in order to see the difference.)

 

[Pat Hartman]

DLB, I hope you don't mind, I modified your db to make it easier to compare the timings. I also added Jon's original query. Notice that the timings change with the order in which the queries are run the first time the db is opened. The timings also change if you rerun the queries.

Jon, I don't understand what the date compare is all about in the second set of queries or why you chose 3 days as the range. However, it does seem to have some dramatic effect on the speed of the query.

 

[mdcory]

Thanks for the help. Haven't had a chance to to mess with it, hopefully tonight.
Thanks again...

 

[Jon K]

Pat,
I think the first time the database is opened and one of the button is clicked, Access needs to maybe open the table and maybe load some/all records into memory, so the next time another button or the same button is clicked, the data or some data are still in memory and it doesn't need the extra overhead.

If you close the database and immediately reopen it and click on one of the buttons, it seems it doesn't need the extra overhead either. I guess it's because the data are still in memory.

So to get a true comparison, we need to test them under the same environment, that is, to compare either when no data is in memory or when the data is already in memory.

To carry out tests under the latter environment is a lot easier because we can simply ignore the first run when the database is started. And it's also more accurate to run each set of queries several times and compare their average figures.


In the first query of my two-query set,
"Query2a"
SELECT [Rec_Dated], [AIM_N7089]
FROM dbo_SouthVariable1
WHERE [Rec_Dated] BETWEEN (SELECT Max([Rec_Dated]) FROM dbo_SouthVariable1) -3 AND (SELECT Max([Rec_Dated]) FROM dbo_SouthVariable1);

I wanted it to return at most 4 records because though the poster said "there is a new record made everyday", I'm not sure whether Saturdays and Sundays are skipped. To return 4 records, I can be sure that, when the queries are run on Monday, the record for Friday is returned. If Saturdays and Sundays are never skipped in the table, then the query can be modified to return only 2 records, since only one record can be Null in AIM_N7089.

As the date field is indexed, this technique has the advantage that the time to retrieve four records for the second query will not increase as the number of records in the table grows.

If you directly click on the above query, you will be amazed to see how very fast it runs, even though it contains two subqueries (or more correctly, the same subquery in two different places.)
.

 

[Pat Hartman]

Jon,
I was amazed that the second set of queries ran so much faster than the first query. The first query was usually slightly slower than my suggested alternative. The answer seems to be that your first query and my suggestion both need to include the second criteria in the "first pass" of the table. Your second set of queries does not. It relies on the member's statement that there will be a record every day. As long as that is true, the second solution provides the fastest time. I even tried indexing the second criteria field to see if that had any impact and it didn't seem to. Knowing that there would be a record every day allows one to take advantage of that fact and use a date range that will include the requested record. Increasing the date range doesn't affect the time significantly so I would suggest increasing the date range to 10 days to be extra safe. You first solution will always work and my solution will always work but your second, more efficient solution will only work if in fact there is never a gap of more than three days without any data entry into the criteria field.
Pat

 

[Jon K]

Pat,
You are quite right about the need to increase the date range to 10 days to be extra safe.

I had missed the sentence in Post #7 by the member: "if today and friday do not have data in the AIM_N7089 column but thursday does I need that data since that would be the most recent" when I assumed that only one record could be Null in AIM_N7089.

Jon