Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2016
    Posts
    1

    Unanswered: Summarising Access Queries

    Hello everyone,
    I have an Access 2013 database of soccer results with my own ratings for each game, for which I've created queries to select the records by the different ratings I've applied.

    I can now get all the totals that I was after for the different types of ratings by running the queries, but I'm struggling to find an easy way to summarise the data in a form/chart/report etc.

    All I need are the total number of records each query returns so I can do some very basic calculations to get the percentage they represent for each rating type, then have that display in a nice chart/report/whatever so I can see at a glance what's happening.

    So for instance if I select all the top-rated teams that won, I'd want to divide that by the total number of records in the top-rated group to get a strike rate. Then I'd also want to summarise the results so I can see how many games finished 2-1 FT, 0-0 etc. as they are rated as well.

    I've been struggling with this for a while and am not finding it easy using Access or Excel for some reason, to the point where I'm looking at using Microsoft Power BI instead.

    However I'm not really happy having to use another piece of software as I'm trying to keep the system "standardised" so it only needs a copy of office to run on a PC.

    Any ideas or advice greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Should be dooable, but depends on the design of your tables.
    Theres very powerful SQL analytical tools such as COUNT SUM AVERAGE and so on. Used in conjunction with GROUP BY. Data can be filtered using WHERE, and you can also limit data in a group by using HAVING.

    Lets say you have columns called result, hometeam, awayteam in a table called fixtures
    Code:
    select result, count (result) from fixtures
    Group by result
    Would give sonething like
    0 - 0, 10
    0 - 1, 6
    0 - 2,3

    Extending that to
    Code:
    select honeteam, result, count (result) from fixtures
    Group by hometeam,  result
    Arsenal 0-0 3
    Aresenal 0-1,2
    Leicester 1-0 4
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    of course if this is in reporting then you could use the similar functions in report / group footers

    ..but be aware that the using lots and lots of domain functions (DCOUNT, DSUM etc) in footers can be a performance hog
    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
  •