Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2015
    Posts
    10

    Unanswered: Combining Queries in a Sports Database

    Dear All good afternoon,

    I am trying to create a sports database to use for statistics analysis and as I am very new to databases I am having a difficult time to move on with this project. I am keeping the records in an excel master file where I gather the odds per game and the result (plus data that are derived from the result, "Over 2,5 goals" e.t.c.) and I have transferred this file to an MS access database.

    My main problem is that I can not combine (probably) queries in a correct way so as to transfer the data to other tables where they could be more useful. What I would like to achieve is let's say to have a table with the number of the results per team.

    So, a team, let's say Barcelona has a home behavior of 13 times "1", 10 times "X" , 2 times "2" and I would like this to be presented in a table per team, for the Spanish league.

    Given that the data is in one column in the master file, how could I write the query to achieve such a result? I can present the times that the team has a home win (using the WHERE clause) but since I have to use it more than one time (for the different results in different columns) I find it confusing...

    If someone would like to help me would be very much appreciated!

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    All depends on the design.....
    But ive got to say importing data from 'an excel master' unless its been normalised rarely makes a goid foundation.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2015
    Posts
    10

    Combining Queries in a Sports Database

    And how could I get an advice about that?

    The excel file is somehow normalized as it doesn't contain double entries or data. Is there any website or tutorial that could teach me a good way to design and build the database?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2015
    Posts
    10

    Combining Queries in a Sports Database

    Thank you for your articles, but can I ask a question?

    If you have a table like the one below, how could you write a query so as to make a new table like the second one?

    What I want basically is to count in the columns of the new table the different values of the C column of the first table and the where clause can not work in this way I think...

    A | B | C
    ----------+-----------+--------------
    1 | 2 | 1
    1 | 2 | Χ
    1 | 2 | 2
    1 | 2 | 1
    1 | 2 | Χ

    A | 1 | X | 2 |
    ----------+-----------+-------------+------------+
    1 | 2 | 2 | 1 |
    2 | 2 | 2 | 1 |

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Mebbe im being thick but I cannot see any relationship between your data and the required output. Perhaps if you try again using different values.
    You may get there with a pivot table and some pre processing, using a query as a feedstock, or a sub query
    Consider using a group by with the vount
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2015
    Posts
    10
    Good morning,

    My table has in the first two columns the names of the teams per match for the last x years. Lets say column A = lazio and column B = roma. Column C contains the outcome of the games historically. So the first two columns have repeated values where the values of the C column are different based on the outcome of the game. I.e. "1", "X", or "2" (home win, draw, away win). What I want is to create a new table where the first two columns will have the name of teams played and in the next colums the count of each outcome. So A & B columns will have the teams and C, D and E will have the count for each outcome. C for home win, D for draw abd E for away win. Is it possible an how can be created. I want to know the methodology mainly. Thanks!

  8. #8
    Join Date
    Oct 2015
    Posts
    10
    Let me do it more simple.

    Column A has the values for an event. Column B has the outcome of the event which can take 3 values. Let's say R1, R2 & R3.

    What I want, is to create a new table which will appear in the first column the values of the column A of the first table, grouped alphabetically (T1 and T2) and in the next three columns to present the count of each outcome.

    Is it more clear now? My database is more complicated but this solution may guide me for the rest of the data.


    A B
    T1 R1
    T2 R2
    T1 R3
    T2 R1
    T1 R2



    A R1 R2 R3
    T1 1 1 1
    T2 1 1 0

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so here's an approach
    I created two tables
    table teams
    Code:
    code
    clubname
    table fixtures
    Code:
    home_code
    away_code
    played_on
    home_score
    away_score
    created a query results
    Code:
    SELECT
       fixtures.home_code,
       fixtures.away_code,
       fixtures.played_on,
       IIf(home_score=away_score,"D",IIf(home_score>away_score,"W","L")) AS home_outcome,
       IIf(home_score=away_score,"D",IIf(home_score>away_score,"L","W")) AS away_outcome
    FROM
       fixtures;
    ..all this does is assign a result code for each outcome (by defintion a home win also equates to an away loss and vice versa, a draw is a draw

    that generates:-
    Code:
    home_code away_code played_on    home_outcome away_outcome
    manc      manu      08-Jan-2012  L              W
    manc      manu      30-Apr-2012  W              L
    manc      manu      09-Dec-2012 	L              W
    manc      manu      22-Sep-2013	 W	L
    manc      manu      02-Nov-2014  W	L
    manu      manc      01-Aug-1936  W	L
    manu      manc      07-Aug-2011  W	L
    manu      manc      23-Oct-2011  L	W
    manu      manc      25-Mar-2013  L	W
    manu      manc      08-Apr-2013  L	W
    manu      manc      12-Apr-2015  W	L
    manu      manc      25-Oct-2015	D	D

    using that query as the feedstock into another query head_to_head
    Code:
    SELECT 
       results.home_code,
       results.away_code,
       results.home_outcome,
       Count(results.home_outcome) AS home_count,
       results.away_outcome,
       Count(results.away_outcome) AS away_count
    FROM
       results
    GROUP BY
       results.home_code,
       results.away_code,
       results.home_outcome,
       results.away_outcome;
    gives the outcome
    Code:
    home_code	away_code	home_outcome	home_count	away_outcome	away_count
    manc      manu  L	2	W	2
    manc      manu	W	3	L	3
    manu      manc	D	1	D	1
    manu      manc	L	3	W	3
    manu      manc	W	3	L	3
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2015
    Posts
    10
    Thank you so much Healdem,

    I wanted to see the approach. I understand now your methodology.

    I get the data from a website to an excel file and I am transforming them to the desired format before I copy them to the Access Database.

    Probably it is more convenient instead of having all the results of the games to one column, to have a separate column for every outcome. In this way would be easier to count each column with the unique outcome.

    I will revert in the afternoon where I will test the codes.

    Thanks again!

  11. #11
    Join Date
    Oct 2015
    Posts
    10
    Good afternoon Healdem,

    The above codes are very useful to present the outcome of a game based on goals (if I am not mistaken), but they do no apply well in my case.

    What I want to achieve is to count the W,D,L for a team when i.e. plays at home side or away side (two separate queries or tables).


    My master table has the below data:

    Date Home_Team Away_Team Outcome
    08-Jan-12 manc manu 1
    30-Apr-12 manc manu 1
    09-Dec-12 manc manu 1
    22-Sep-13 manc manu X
    02-Nov-14 manc manu X
    01-Aug-36 manu manc X
    07-Aug-11 manu manc 2
    23-Oct-11 manu manc 2
    25-Mar-13 manu manc 2
    08-Apr-13 manu manc 1
    12-Apr-15 manu manc 1
    25-Oct-15 manu manc 1



    The desired outcome is shown below (When team plays at home):

    Home_Team Outcome_1 Outcome_X Outcome_2
    manc 3 2 0
    manu 3 1 3

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So rejig the query
    Id probably look at modifying tge results query, so instead of allocating W, L or D as home and away outomes id create a 1 or zero by whatever means you need to define

    Eg
    .... iif (home_score >away_score,1,0) as win, iif (home_score=away_score,1,0) as draw, iif (home_score<away_score) as lost


    Then modify the head to head query so it summed those values

    You coukd refine the draw to differentiate between a draw and a score draw.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In the absence of your table design i just went with the flow. To me it makes no sense to store win lose draw, but ibstead store the actual score and then derive what you want when you want. But ultimately its your system, in my books youve got enough to make an effort if you want to persist with your approach. Maje an effort to understand what is in post #9 and then apply it to your design. Frankly allocating 1 as win, x as draw and 2 as lose seems needlessly obtuse

    Intrinsically the process is the same
    First query reprocess the data, defines win draw lose
    Second query sums those analysis values
    Last edited by healdem; 03-03-16 at 18:31.
    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
  •