MS Access question

I'm trying to create a query in MS Access to return a list of records that have the most recent date in one of the fields. Using Max or Last aggregate isn't doing it. How do I get the correct results?

Thanks!

Can you not just use ORDERBY ?

That would still return all the records. I only want the latest records so I can display them on the page in the What's New section.

okay, I see...

In the criteria, what about BETWEEN [Date] AND [enddate]...

or it might be easier to do this with whetever language your using though...

Max should work fine with Access. Here's a snippet of SQL I've used in a ColdFusion app:

Code:

---

SELECT MAX(ListOrder)
AS MaxListOrder
FROM tblFoo

---

What does MAX do?
I was going to implement somthing similar, but I wanted to retrive the latest 5 articles i just used a loop while which went 5 times....

The way I've used it above, MAX returns the maximum value for ListOrder.

If you wanted to return the top five values, then a loop is probably the best way to do it, or perhaps a compounded select statement (MAX, MAX-1, MAX-2 etc.)

Thanks for that Hillsy :)

But I reacking the orig. question was for access as a standalone though...

Hmmm - yeah it could have been, reading it again. That could make the loop hard...

However you can still use "standard" SQL in Access queries so maybe a select statement is the way to go.

Raynebair - care to give us some more info? :D

For some strange reason i thought Raynebair was using ASP with access :D ohhh well, SQL can do it just as well...

Yes, I'm using ASP with access and wanted to display a list of records that have the latest date.

This would be easier than going into code and changing which date I wanted to return each time I add new data.

So If I enter new data on 2-2-02, I would rather it recognize that as the latest date and only return records with that date. Easier than having to go into code and change the criteria to that specific date.

I was going to have the query in access and use asp to run that query and display the results.

When I tried to use max, it returned all my. Maybe I'm just not setting up the query quite right.

The sql syntax would be what?

Code:

---

SELECT MAX(ListOrder)
AS MaxListOrder
FROM tblFoo

---

This only returns one value. Do I have to do this, then use that value in another query to get all the records that have that max value?

Thanks.

Quote:

---

Originally posted by raynebair
Yes, I'm using ASP with access and wanted to display a list of records that have the latest date.

<snip>

The sql syntax would be what?

Code:

---

SELECT MAX(ListOrder)
AS MaxListOrder
FROM tblFoo

---

This only returns one value. Do I have to do this, then use that value in another query to get all the records that have that max value?

---

That's what I'd do. There are two parts to this problem:

1) Find the latest date.
2) Return all records with that date.

Is there a problem with using two queries?

You might be able to combine it into one query though. The following subquery is psuedo-code as I'm nowhere near an ASP/Access system to actually test it. However it works in Access 2000 - returns any records with the Max date:

Code:

---

SELECT Record
FROM tblFoo
WHERE Date IN
(SELECT Max(tblFoo.Date) FROM tblFoo);

---

I think it's do-able... :)

Thanks hillsy! that works. In access I did a query that got the max date. then I did a query linking my table to that query where the dates were the same.

Now I can just get asp to use the query that pulls all the records in.

Easy...why couldn't I figure it out? Oh well. :)