Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (Access 97)

    I'm sure the answer to this is easy, but like all problems when you don't know the answer.....

    OK. Two tables: tblOrders, primary key = .Order_ID, and tblOrderNotes, primary key = .Note_ID.

    Tables linked by tblOrders.Order_ID ------ tblOrderNotes.Order_ID

    I'm sure you've gathered by now that one table holds all the orders, and the other allows users to add multiple notes regarding the progress of any/each order.

    I have a form driven by a query that displays the order. I want to display the latest note for that order. The notes table has a date/time stamp field, but i cant work out how to write the query. I am sure it involves an aggregate function along the way, but half of me is thinking i'm going to have to have two queries, one to work out which is the most recent note for any given order number, and then use that as a recordsouce for the second query behind the form....

    All comments appreciated...

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access 97)

    Peter: Not knowing all the intricacies of your situation, a starting point might be to use th MAX function on the date/time field. This will give you the latest date. Or you could sort all your dates in descending order to give you the latest first. HTH.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access 97)

    Thanks for your reply BC. I was hoping i had made myself clear, but perhaps i have not. Basically, what i want is to run a query linking two tables together, showing each row from one table (tblOrders), and the row with the highest [date] in the other (tblOrder_Notes)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access 97)

    Peter, I did this in a form. On the date field, in Properties on the Data Tab, in the Order By box, I placed the name of the field with DESC at the end of the field name.

    Example: dteLastUpdate DESC

    This worked for me.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Problem (Access 97)

    You could try using a table joined to a query on the second table. The second query would be a totals query along these lines:

    SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
    FROM Orders
    GROUP BY Orders.CustomerID
    ORDER BY Orders.CustomerID;

    That would give you the maximum order date for each customer, for example, and you could link it to another table through the customerID.
    Charlotte

Posting Permissions

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