Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Finding Highest Total (2000)

    I have a table that has a date, part number, and quantity. I would like to find what the highest 3 months in a row total quantity is in the last 12 months. Is this possible?
    Thanks,
    John

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

    Re: Finding Highest Total (2000)

    Do you want to look at calendar months only (e.g.. March - May 2008), or at any 3 month period (e.g. March 22, 2008 - June 21, 2008) to determine the highest total?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Finding Highest Total (2000)

    I was going to say only look at calendar months but you bring up a great idea, I think I want now any 3 month period.
    Thanks Hans, Great Idea!!
    John

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

    Re: Finding Highest Total (2000)

    See the attached sample database. It has a table tblParts with a date field, part number field and quantity field. The table has been populated with random values.
    The query qryUniqueDates returns a list of unique dates from tblParts within the last 9 months (we're going to look 3 months back from these dates, so this covers the last year).
    The query qry3MonthTotals calculates the total quantity for the period of 3 months up to each of these dates.
    The query qryMaxTotal returns the highest 3 month total and the corresponding date.
    In the sample database, the maximum is reached for February 29, 2008. This refers to the period from November 30, 2007 to February 29, 2008.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Finding Highest Total (2000)

    I should have mentioned that what I needed was the highest three month total for each part number.
    Thanks,
    John

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

    Re: Finding Highest Total (2000)

    <img src=/S/burnup.gif border=0 alt=burnup width=31 height=31> Couldn't you have said that in your first post? <img src=/S/burnup.gif border=0 alt=burnup width=31 height=31>

    See modified version.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Finding Highest Total (2000)

    Works like a charm. I am sorry that I did not include everything I was looking for in my first post. I will do better next time.
    Thank you for your help.
    John

Posting Permissions

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