Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2006
    Posts
    6

    Unanswered: Avg average groups of data combine similar entries access 2003

    Hello, I have MS Access 2003


    I Have log files that give me data that I use to calculate % yeild for each hour.
    I have successfully imported the data into MS access, but now i'm at a loss for how to get averages for my data.
    As you can see below in my supplied data, I have a % yeild calculated for each hour. what I would like is to have:
    1: a report with each hours individual data AND at the bottom of every 12 hr period the Average value of those 12 entries
    2: a report with (no Hourly data) but the average values for the 12 hour period And then the average value for the month/year



    Here is a sample of the data that I have

    GrossPctYeild Shift Hour Shift Date
    53.29% Day 7 3/21/2006 0:00
    54.84% Day 8 3/21/2006 0:00
    48.97% Day 9 3/21/2006 0:00
    56.53% Day 10 3/21/2006 0:00
    53.54% Day 11 3/21/2006 0:00
    54.83% Day 12 3/21/2006 0:00
    60.58% Day 13 3/21/2006 0:00
    58.52% Day 14 3/21/2006 0:00
    59.32% Day 14 3/21/2006 0:00
    62.96% Day 15 3/21/2006 0:00
    54.19% Day 16 3/21/2006 0:00
    63.44% Day 17 3/21/2006 0:00
    64.67% Day 18 3/21/2006 0:00
    64.58% Night 19 3/21/2006 0:00
    67.25% Night 20 3/21/2006 0:00
    68.84% Night 21 3/21/2006 0:00
    69.16% Night 22 3/21/2006 0:00
    69.32% Night 23 3/21/2006 0:00
    70.49% Night 0 3/21/2006 0:00
    57.22% Night 1 3/21/2006 0:00
    71.98% Night 2 3/21/2006 0:00
    69.23% Night 3 3/21/2006 0:00
    70.86% Night 4 3/21/2006 0:00
    69.86% Night 5 3/21/2006 0:00
    71.04% Night 6 3/21/2006 0:00
    67.75% Day 7 3/22/2006 0:00
    72.06% Day 8 3/22/2006 0:00
    76.85% Day 9 3/22/2006 0:00
    75.11% Day 10 3/22/2006 0:00
    73.34% Day 11 3/22/2006 0:00
    74.05% Day 12 3/22/2006 0:00
    74.70% Day 13 3/22/2006 0:00
    73.07% Day 14 3/22/2006 0:00
    73.71% Day 15 3/22/2006 0:00
    74.42% Day 16 3/22/2006 0:00
    69.10% Day 17 3/22/2006 0:00
    73.77% Day 18 3/22/2006 0:00



    It would be nice if it looked like this

    GrossPctYeild Shift Hour Shift Date
    53.29% Day 7 3/21/2006 0:00
    54.84% Day 8 3/21/2006 0:00
    48.97% Day 9 3/21/2006 0:00
    56.53% Day 10 3/21/2006 0:00
    53.54% Day 11 3/21/2006 0:00
    54.83% Day 12 3/21/2006 0:00
    60.58% Day 13 3/21/2006 0:00
    58.52% Day 14 3/21/2006 0:00
    59.32% Day 14 3/21/2006 0:00
    62.96% Day 15 3/21/2006 0:00
    54.19% Day 16 3/21/2006 0:00
    63.44% Day 17 3/21/2006 0:00
    64.67% Day 18 3/21/2006 0:00
    _____________________________________________
    57.32% Day AVG 3/21/2006


    64.58% Night 19 3/21/2006 0:00
    67.25% Night 20 3/21/2006 0:00
    68.84% Night 21 3/21/2006 0:00
    69.16% Night 22 3/21/2006 0:00
    69.32% Night 23 3/21/2006 0:00
    70.49% Night 0 3/21/2006 0:00
    57.22% Night 1 3/21/2006 0:00
    71.98% Night 2 3/21/2006 0:00
    69.23% Night 3 3/21/2006 0:00
    70.86% Night 4 3/21/2006 0:00
    69.86% Night 5 3/21/2006 0:00
    71.04% Night 6 3/21/2006 0:00
    _____________________________________________
    68.32% Night AVG 3/21/2006


    67.75% Day 7 3/22/2006 0:00
    72.06% Day 8 3/22/2006 0:00
    76.85% Day 9 3/22/2006 0:00
    75.11% Day 10 3/22/2006 0:00
    73.34% Day 11 3/22/2006 0:00
    74.05% Day 12 3/22/2006 0:00
    74.70% Day 13 3/22/2006 0:00
    73.07% Day 14 3/22/2006 0:00
    73.71% Day 15 3/22/2006 0:00
    74.42% Day 16 3/22/2006 0:00
    69.10% Day 17 3/22/2006 0:00
    73.77% Day 18 3/22/2006 0:00
    _____________________________________________
    73.16% Day AVG 3/22/2006




    And also nice if I could have a seperate Report that had only the 12hr avgs and perhaps a monthly avg:

    57.32% Day AVG 3/21/2006
    68.32% Night AVG 3/21/2006
    73.16% Day AVG 3/22/2006
    __________________________________________
    62.56(guess)% All AVG March 2006




    I've looked all over the place to figure out how to do this, also I was hoping to do this with expressions, or query/reports...

    I'm not very good yet a VBA for access, but if that the only way to accomplish this I'm gonna have to learn...



    Also so you know what I've tried allready, I made the generic form, and tried to add a group footer, then i added the a text box, where i put in the expression =Avg([GrossPctYeild]), but When I saved then opened I get:
    Data type mismatch in criteria expression. (Error 3464)

    And when I use the wizard, and i do grouping by first "shift date" by day, then secondly "shift" by alphabetical, I don't get a summary options box for my GrossPctYeild, but it does give me the option to get the average of the hours which does work, but when i try to modify the hours box in design vew to average grosspctyeild instead, I get the same Datatype mismatch error.


    Thanks in advance for any help you can give.
    -Carl

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    quick and dirty solution... do it as a report, using the date period as a grouping, place a control in the report footer with something like =avg[mycolumn], bearing in mind you would use week, month, year, quarter as grouping elelemnts so you would have as many periods of data you require. Mind you if you do it in a report then yuou dont need to use the Sq aggregate functions int he frooter, you coudl do your own calcualtions and save the tooing and frooing on the db, by placing some code in the detail format section, remembering to set values int he group footer, and zero in the group header sections.

    you can use SQL aggregate fucntions in a query if desired


    you could do some nasty tricks using a switch in the reports on format event to switch off reporting (set cancel = true) if its not, say this month.

    bear in mind you could do a sub report which used an SQL aggregate query to pull in values for say previous eweeks/months or what ever.
    And you could use that as a feed into say a MS Chart object embedded in a report.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2006
    Posts
    6
    Thanks Healdem,

    I tried that again using the format you posted , =avg[column] and i've previously tried it with other formats: =avg(<<exp>> [column] <<exp>>)

    and no mater how i try to format it i get the Data type mismatch in criteria expression. (Error 3464)

    how can i get around that, and whats causing it?

    Thanks again, Carl

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I think it's because you try to do an avg on non-numeric data.
    You can not do an avg on strings or data type like this.
    Maybe you should try to create a module that convert your string or date into integer.

    Function Example
    Function strToInt(str As String) As Integer
    strToInt = CInt(Value(str))
    End Function
    USING EXAMPLE
    SELECT avg(strToInt(string)) FROM TABLE
    I didn't test it but I think it's gonna work

    Can you show us an example of the data in the table(s) and the table(s)' structure ?

  5. #5
    Join Date
    Nov 2006
    Posts
    6
    Here is some more background info:



    I have a CSV that i am linked to that is my main table,

    then i perform one query of that table to calculate my hourly percentages and to convert my values od month,day,year into one column simply date

    then i have another query that queries my first query to break the days into 12 hour shifts starting at 7am

    I know that that may be confusing, but couldn't figure out hour to add the "day shift" or "night shift" column with just one query

    so i wasnt sure if going throuh all those steps could cause the data mismatch.

    -

    Carl

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I know that that may be confusing, but couldn't figure out hour to add the "day shift" or "night shift" column with just one query
    Huh... hour to add the "day shift" ?

    What do you mean?

    Show me both of your queries ...

  7. #7
    Join Date
    Nov 2006
    Posts
    6
    here is a trunicated CVS data file i'm using plus the mdb with the querys


    you may need to redirect the link to get the mdb to sync with it

    Below are the two fields i created and in which query they're located



    Query1st:

    Date: [Month] & "/" & [Day] & "/" & [Year]
    Shift: IIf([Hour]>=7,IIf([Hour]<19,"Day","Night"),"Night")


    Query2nd

    Actual Date: IIf(IsError(DateAdd("d",0,[Date])),CVDate(1/1/2000),DateAdd("d",0,[Date]))
    Shift Date: IIf(IsError(IIf([Hour]>=7,DateAdd("d",0,[Date]),DateAdd("d",-1,[Date]))),CVDate(1/1/2000),IIf([Hour]>=7,DateAdd("d",0,[Date]),DateAdd("d",-1,[Date])))
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2006
    Posts
    6
    Query1st:

    //reformat seperate month day and year into one date field
    Date: [Month] & "/" & [Day] & "/" & [Year]

    //add shift field if hour is inbetween 7 am and 7 pm make it "day" else make it "night"
    Shift: IIf([Hour]>=7,IIf([Hour]<19,"Day","Night"),"Night")


    Query2nd

    //reformat the string date, into the date format, use the correct date regardless of shift
    Actual Date: IIf(IsError(DateAdd("d",0,[Date])),CVDate(1/1/2000),DateAdd("d",0,[Date]))

    //reformat the string date, into the date format, alter the date based on shift: ie, 10-21-06 would start at 7 AM,,,,,,,,,,,,, the actual hour of 10-21-06:6AM would have a shift date of 10-20-06
    Shift Date: IIf(IsError(IIf([Hour]>=7,DateAdd("d",0,[Date]),DateAdd("d",-1,[Date]))),CVDate(1/1/2000),IIf([Hour]>=7,DateAdd("d",0,[Date]),DateAdd("d",-1,[Date])))

  9. #9
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82
    if i understand correctly, have a look at this simple query and report.

  10. #10
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82
    if i understand correctly, have a look at this query and report.

    change/add as req
    Attached Files Attached Files

  11. #11
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    will check that after job...

  12. #12
    Join Date
    Nov 2006
    Posts
    6
    "L" Plate, Thanks, I compared what we were doing and i was converting my data to percent at the first stage, and then trying to average the percents

    I wnt through my sheet and converted all of my data off of percent.. and then on the erport itself converted it to percent and everything works grate now


    Thanks everyone for your support!!!

  13. #13
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    dbForums kick ass!!

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by MrGlass
    ....."L" Plate, Thanks, I compared what we were doing and i was converting my data to percent at the first stage, and then trying to average the percents.....
    ....the key lesson here is to do the data processing & marshalling using SQL, and let the presentation layer (form / reprot etc) to do the formatting. Some simple processing is fine int he presentation layer, but its selecting the right tool to do the job.... mind you there are losts of times when you can push calcualtions onto the presentation layer.
    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
  •