Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015
    Posts
    1

    Unanswered: MS Access Multiple Field Average with criteria?

    Hi All,
    could use some help with what initially seemed liike a pretty easy query, that I just cant seem to think my way through at the moment…
    I have a table, with lots of data (many many many records) that I have calculated and filtered into a single query… all good so far….

    Now I need to work out the average 'Sales' per hour, per day, per financial week for the current year and those that came previously.... eg, if the DateTime is 23/12/2013 10:01:00, then i need the average sales for 2013/2012/2011, where the week number is 52, the day of the week is 6 (Friday) and the TimeSlot is 10:01...
    I'm pretty sure that i need to do something with a subquery for this one, as none of the more straight forward methods i've tried seem to work, i do know how to perform this calculation in Excel, but i can't for the life of me work out how to transfer this to Access..=AVERAGEIFS(Sales,Years,"<="&C3,Weeks,D3,D ays,E3,TimeSlot,F3)

    Sorry about the formatting, but i've included a sample of the query data... The year column actually runs 2011-2015, the Weeks Column runs 1-52/53, the days column runs 1-7 and the time slots run 00:01-23:01...

    1____________A____________ B_______C________D___________E_________F
    2______DateTime___________Years___Week__DayOfWeek_ __TimeSlot___Total Sales___Expected Result
    3______18/12/2011 10:01____ 2011____52_______1________ 10:01:00______40___________40.00
    4______20/12/2011 10:01____ 2011____52_______3________ 10:01:00______37___________37.00
    5______21/12/2011 10:01____ 2011____52_______4________ 10:01:00______38___________8.00
    6______22/12/2011 10:01____ 2011____52_______5________ 10:01:00______40___________40.00
    7______23/12/2011 10:01____ 2011____52_______6________ 10:01:00______41___________41.00
    8______19/12/2011 10:01____ 2011____52_______2________ 10:01:00______39___________39.00
    9______24/12/2011 10:01____ 2011____52_______7________ 10:01:00______34___________34.00
    10_____23/12/2012 10:01____ 2012____52_______1________ 10:01:00______37___________38.50
    11_____24/12/2012 10:01____ 2012____52_______2________ 10:01:00______38___________38.50
    12_____25/12/2012 10:01____ 2012____52_______3________ 10:01:00______16___________26.50
    13_____26/12/2012 10:01____ 2012____52_______4________ 10:01:00______30___________34.00
    14_____27/12/2012 10:01____ 2012____52_______5________ 10:01:00______34___________37.00
    15_____28/12/2012 10:01____ 2012____52_______6________ 10:01:00______36___________38.50
    16_____29/12/2012 10:01____ 2012____52_______7________ 10:01:00______32___________33.00
    17_____22/12/2013 10:01____ 2013____52_______1________ 10:01:00______40___________39.00
    18_____23/12/2013 10:01____ 2013____52_______2________ 10:01:00______31___________36.00
    19_____24/12/2013 10:01____ 2013____52_______3________ 10:01:00______35___________ 29.33
    20_____25/12/2013 10:01____ 2013____52_______4________ 10:01:00______14___________ 27.33
    21_____26/12/2013 10:01____ 2013____52_______5________ 10:01:00______29___________ 34.33
    22_____27/12/2013 10:01____ 2013____52_______6________ 10:01:00______36___________ 37.67
    23_____28/12/2013 10:01____ 2013____52_______7________ 10:01:00______37___________ 34.33
    24_____27/12/2014 10:01____ 2014____52_______7________ 10:01:00______37___________ 35.00
    25_____21/12/2014 10:01____ 2014____52_______1________ 10:01:00______38___________ 38.75
    26_____22/12/2014 10:01____ 2014____52_______2________ 10:01:00______37___________ 36.25
    27_____23/12/2014 10:01____ 2014____52_______3________ 10:01:00______37___________ 31.25
    28_____24/12/2014 10:01____ 2014____52_______4________ 10:01:00______37___________ 29.75
    29_____25/12/2014 10:01____ 2014____52_______5________ 10:01:00______15___________ 29.50
    30_____26/12/2014 10:01____ 2014____52_______6________ 10:01:00______27___________ 35.00





    Any pointers, methods or assistance in making my brain work again would be happily and gratefully received!!!

    Regards

    Brujah

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need the average of a specific time period and compare it to the same time period in specified year

    use the AVG function in a query
    the tricky part is setting the mechanism that identified the GROUP BY and WHERE / HAVING clauses

    if the supplied names are your column names then you are creating serious problems for yourself going forward.. don't use reserved words and or symbols for table / column names

    so thats going to be something like
    SELECT year(sales_period), avg(sales_value) FROM my_table
    GROUP BY year(sales_period)
    HAVING week_no = 52 and timeslot = '10:01:00'

    ..that assume timeslot is a string value.


    bear in mind a database works by extracting data as required when required the is no concept of adjacency of data. if yo9u want to consume the data in a report then what you could do is extract all the data for time bands then report it as required


    if you need to analyse by the minute, and / or hour and / or day and /or week then you may need separate queries. or you coudl fold up that sumamaristion by selctigfn which form / report consumes the query. have the query average by the minute but have different reports that show that data by thge hour / dya / week / whatever. providing you have the 3 comparatives you should be fine

    so the query could be

    SELECT year(sales_period), weekno, dayno, timeslot, avg(sales_value) FROM my_table
    GROUP BY year(sales_period), weekno, dayno, timeslot




    this is one of the cases when it may be worthwhile to break one of the rules of normalisation and summarise the historic data (say previous years) so that you don't do uneccessary processing on what is static (historic) data.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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