Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    25

    Unanswered: Custom Query/Reports

    I have a table with many records in associated with customers.

    I want to provide a one line query/report for the latest data per customer number. Example of this is

    Record 1

    Date: 13/01/08
    Customer No: 12
    Cover Level: 2

    Record 2

    Date: 14/01/08
    Customer No: 13
    Cover Level: 3

    Record 3

    date: 12/01/08
    Customer No: 12
    Cover Level: 3

    What I want using the above would be a report to only show the following:

    customer No 13 - Cover Level 3 from 14/01/08
    customer No 12 - Cover Level 2 from 13/01/08

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This is generally done with 2 queries. The first is a totals query that groups by customer and returns the max date. The second query joins the first query to the original table on those 2 fields, enabling you to return the other values from the record with the max date. Your report would then be based on the second query.
    Paul

Posting Permissions

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