Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max of Date query (XP/2000)

    I'm getting in a pickle with what should be a simple query.

    I have a table (tblExams) containing survey info for a number of sites.

    An examination (in tblExams) is uniquely defined by its reference number (SiteRef) and exam date (ExamDate). Sites undergo re-examination, the date (DueDate) specified according to a rating (Score). eg Score = 1, DueDate = ExamDate + 10 years, Score = 2, DueDate = ExamDate + 5 years.

    The first thing I need to do is create a query containing only the most recent examination for each site (SiteRef) but I don't seem to be able to do this. In additional to the SiteRef, ExamDate, DueDate and I need to include a few other fields.

    Using the query and 'from date' and 'to date' parameters passed from a form I can then generate a list of exams due to be re-examined during a specific period in the future.

    I'm stuck with the query, I've tried using Max but my query is still returning all the records in the table and to be honest the I get a bit confused by the options when you use the view 'Totals' option in a query .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max of Date query (XP/2000)

    Hi Darsha,

    You need to create two queries.

    1) Create a query based on tblExams.
    Add only the SiteRef and ExamDate fields to the query grid.
    Select View | Totals to change the query to a totals query.
    Set the Total option for ExamDate to Max (for SiteRef, it remains the default Group By).
    This query returns the most recent ExamDate for each SiteRef.
    Store this query as qryMaxDate.

    2) Create a query based on tblExams and on qryMaxDate.
    Join the tables on SiteRef - SiteRef and on ExamDate - MaxOfExamDate.
    Add some or all fields from tblExams to the query grid.
    Add calculated fields, if necessary.
    Note: this query will not be updateable. You can use it to display records in a form or report, but not to edit records.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max of Date query (XP/2000)

    Perfect

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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