Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    26

    Unanswered: how to display the last order date

    Hi all I have a customer table with an address table and an Order table with a OrderDate column .
    I would like to create a Query that would display the customer address and only the Last order made.
    So far I was only able to display the address and and all the Order dates .

    Thanks In advanced
    Sorry This should of been posted under Microsoft Access as this is for an access query
    Last edited by Viper5646; 05-25-13 at 02:26.
    VIPER

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I moved this thread from the MySQL to the MS-Access forum for you.

    Check out the Max() function, it is your friend!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2010
    Posts
    26
    Thanks Pat for moving the threat.
    The Max Function works perfect with two tables the Customer table and Order
    Table but when I try to add a third table the OrderDescription which is related to the Order table It does not work .
    This query is used only with two tables the Order and customer :
    Code:
    SELECT Customer_Tbl.ID, IIf(IsNull([Address]) Or [Address] Like "",[Last_Name] & " " & [First_Name],[address]) AS customerlist, Max(Order.OrderDate) AS OrderDate
    FROM Customer_Tbl RIGHT JOIN Order ON Customer_Tbl.ID = Order.CustomerID
    GROUP BY Customer_Tbl.ID, IIf(IsNull([Address]) Or [Address] Like "",[Last_Name] & " " & [First_Name],[address]);
    This is the Query when I try To add the order description table.

    Code:
    SELECT Order.CustomerID, Max(Order.OrderDate) AS OrderDate, OrdrDisc.msrShort
    FROM Order RIGHT JOIN (Customer_Tbl RIGHT JOIN Order ON Customer_Tbl.ID = Order.CustomerID) ON OrdrDisc.DiscID = Order.dscID
    GROUP BY Orders.CustomerID, OrdrDisc.msrShort
    ORDER BY Max(Order.OrderDate) DESC;
    VIPER

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you want to show every row's description with the max order date, or only the description for the row with the max order date? As a side note, if I can't tell neither can Access and that's the problem!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2010
    Posts
    26
    I Want To display the following for each Max order Date.

    OrderDate,Customer,Description
    VIPER

  6. #6
    Join Date
    Mar 2010
    Posts
    26
    Thanks for your help
    Soled it by using a sub Query

    Code:
    SELECT OrderStatus.CustomerID,Order.msrShort,OrderStatus.OrderDate AS [Order Date]
    FROM Order LEFT JOIN OrderStatus ON Order.mesrID = OrderStatus.msrID
    WHERE (((OrderStatus.OrderDate)=(SELECT MAX(OrderStatus2.OderDate) FROM OrderStatus OrderStatus2 WHERE OrderStatus2.CustomerID = OrderStatus.CustomerID)));
    VIPER

Tags for this Thread

Posting Permissions

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