Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24

    Angry Unanswered: Problem with LAST aggregate in a query

    Hi,

    Background:
    I have a requisition database for requesting and tracking parts ordered. When I first set the db up, I pre-populated a History table with quite a large amount of previous data (from paper records which didn't include some of the information now required). For this reason, whenever I am searching previous orders, I merge the information from the History table with that of my active Order_List table in a query. This master query has every part ordered and is sorted by Company, PartNumber, DateOrdered

    Problem:
    Because of the amount of previous orders, I group the information into parts in another query, which then shows how many times each part has been ordered. You can then expand a part to see all the orders of that part in a separate form. The field I am having trouble with is my LastCost field. Basically, I want to display the last price paid for each part (not necessarily the most expensive, as some things actually get cheaper!!). I am trying to use the LAST aggregate total in my second query - ie Last([Cost]), but it doesn't return the last value that the parent query has listed (ie in date order). This is driving me mad!!

    Can anyone advise on a way around this?

    Is it possible, say, in the criteria of the second query for the LastCost field to base it on the Max of the DateOrdered?

    PLEASE HELP ME !!!!!

    Many thanks,

    Qpid.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    LAST returns the last insterted row in the table. To get the last (with regards to date field value) you want something like:

    Code:
    SELECT MyProd, MyLastPrice
    FROM MyTable
    WHERE DateOfSale = (SELECT MAX(DateOfSale) FROM MyTable AS A WHERE A.MyProd = MyTable.MyProd)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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