SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!

  2. #2
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you not just use ORDERBY ?

  3. #3
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  5. #5
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  6. #6
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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....

  7. #7
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.)
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  8. #8
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that Hillsy

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

  9. #9
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  10. #10
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For some strange reason i thought Raynebair was using ASP with access ohhh well, SQL can do it just as well...

  11. #11
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Last edited by hillsy; Jan 18, 2002 at 06:34.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  13. #13
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •