Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Burgas, Bulgaria
    Thanked 0 Times in 0 Posts

    LastUpdated (Access 2000)

    I need to find out the first two invoices after a client has been created.I use the field LastUpdated for this purpose.So i can easily find the first time when the client has an invoicedate, but how can i know when the client has a second invoice after the first one ? I need to sum up the liters for the first and the second invoice.
    For example a client as an LastUpdate field of 01.01.2006. The invoicedate is always the same, 01.01.2006 The liters are 100
    If there is another invoice, i.e. another invoicedate, i need to sum up the liters for the first and the second invoice.Is that possible ?
    My query is :
    SELECT customers.Customerid, orders.invoicedate, customers.LastUpdated, [order details].liters
    FROM customers INNER JOIN (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)
    GROUP BY customers.Customerid, orders.invoicedate, customers.LastUpdated, [order details].liters
    ORDER BY customers.LastUpdated DESC;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: LastUpdated (Access 2000)

    Try this:

    SELECT Orders.CustomerID, Sum([Order Details].Liters) AS SumOfLiters
    FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
    WHERE Orders.OrderID In (SELECT TOP 2 t.OrderID FROM Orders AS t WHERE t.CustomerID = Orders.CustomerID ORDER BY t.LastUpdated DESC)
    GROUP BY Orders.CustomerID;

Posting Permissions

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