Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Most recent Cost (2003 All Updates)

    I have a table that has records of a series of stock purchases with date and cost of each stock item. As each bill from a supplier is recorded separately in the table, obviously there could be more than one purchase of an item over a period of time eg 2 in January for $10 each and 3 in June for $15 each. Is there a way to have a query show the average cost per item of only the most recent purchase for each item - ie the June one in this example
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Most recent Cost (2003 All Updates)

    Does the purchase date field record only the month pf purchase, or does it record the exact date (so that you want to group by date)?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Most recent Cost (2003 All Updates)

    Records the full date
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Most recent Cost (2003 All Updates)

    1) Start by creating a query based on your table.
    Add the * and a calculated column

    M: [PurchaseDate]-Day([PurchaseDate])+1

    Replace PurchaseDate with the actual name of the field in your table. This column will calculate the month of purchase (in fact the first day in the month).
    Save this query as (for example) qryPurchasesByMonth.

    2) Create a new query based on qryPurchasesByMonth.
    Add only the item field and M to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Set the Total option for M to Max.
    This query will return the month of the most recent purchase for each item.
    Save this query as (for example) qryMaxMonth.

    3) Create a new query based on qryPurchasesByMonth and qryMaxMonth.
    Join them on the item field and on M vs MaxOfMonth.
    Add the item field and the cost field from qryPurchasesByMonth to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Set the Total option for the cost field to Avg.
    This query will return the average cost per item for the most recent month of purchase.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Most recent Cost (2003 All Updates)

    Have a look at the attachment. I have imported MYOB_Purchases and MYOB_ItemPurchaseLines from Clearwater.

    Then I use three queries.

    Query1 joins the two tables and adds a calculated that drops all dates back to the first of their month.
    Query2 find the max of these collapsed dates for each item.
    Query3 joins query1 back to query2 and finds the average price for each item.
    Attached Files Attached Files
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Most recent Cost (2003 All Updates)

    Thanks again both of you for your help and interest- I'll give it a shot

    Steve
    PS believe it or not John, its not MYOB for a change but ODBC from Quick Books
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Most recent Cost (2003 All Updates)

    I imagine that Quickbooks will have a similar structure.
    Regards
    John



  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Most recent Cost (2003 All Updates)

    Hans/John
    Thanks again. I assumed MAX would come into it somewhere but wasnt sure how- Much appreciated

    John, I am sure I can get this to work with Quick Books but surprisingly the structure is very different - not quite flat but very non-normalised. However still workable
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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