Complicated Query

[Pettor]

Dear All good morning,

I am new to databases and I have a question that seems a lot complicated and difficult to me. I have a master table where the first two columns (A&B) have a lot of repeated values and a third column (C) where there are values that are related to the first two columns (an outcome of an experiment).

What I want is to create a new table where there will be four columns, the first will have the values of the column A of the first table and the next three columns will count the values of the column C of the first table again. So, I want to present in different columns the outcomes of the experiments of the values of the column A.

Is it possible?


[TABLE="width: 356"]
<tbody>[TR]
[TD="align: center"]VALUE A[/TD]
[TD="align: center"]VALUE B[/TD]
[TD="align: center"]VALUE C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]VALUES AB[/TD]
[TD="align: center"]VALUES CA[/TD]
[TD="align: center"]VALUES CB[/TD]
[TD="align: center"]VALUES CC[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[mistrellis]

I'm a little confused. Could you clarify what the data represents in the column VALUES AB of your second table?

 

[Pettor]

Yes of course, let me make 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.

(Just a simple question, your name seems like a Greek one. Are you from Greece?)

[TABLE="class: cms_table, width: 356"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]T1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]R1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]T2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]R2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]T1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]R3
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]T2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]R1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]T1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]R2
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 356"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]R1
[/TD]
[TD="align: center"]R2
[/TD]
[TD="align: center"]R3
[/TD]
[/TR]
[TR]
[TD="align: center"]T1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]T2
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mistrellis]

Ok thats more clear:
this will involve creating five queries:
Query 1 (qry_Val) contains the grouped value of column A

T1

T2

<caption> qry_Val </caption><thead>
[TH="bgcolor: #c0c0c0"] Value A [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

We now need to make three queries for each of the results R1 R2 R3

Create a grouped query with the following from your table:
ValueA - Group By
ValueB - Count
ValueB - Group By ( and in criteria type "R1")

Repeat the exercise and save each query as qry_R1 etc making sure that you change the criteria in each one.

Lets stop here to make sure your'e still with me.

(The name isn't greek, just the first thing that came into my head !!!)

 

[Pettor]

Thank you so much, I think I got the point.

I will check the codes in the afternoon and I will revert but I think this is what I wanted, to understand the process.

 

[Pettor]

Good afternoon Mistrellis,

So far so good. All queries are done with excellent results. How can we move on?

 

[Pettor]

Let me present you the whole project because there are a few parameters more. My main goal is to create a football database from where I will get statistical data for each team.

I have a master table with past football results (fixtures) and my main goal is to make a new table, out of this master table, where I will retrieve statistical data for all the teams.

There are two options. A team can play at Home or Away side and they are shown respectively, in the fixtures table, either in the column A (Home Team) or in the B column (as Away Team).

In the new table I want to present all the teams alphabetically and in the next columns the statistical data related to them. I have to retrieve data for the Win, Draw or Lose stats, the number of goals and so on.

So, besides the count function, which is very useful, I am going to need also a way where the data will be presented as a fraction of the total matches of the teams. It seems more complicated but let's move the way you have

thought and I will adjust the queries according to the project.

 

[mistrellis]

I wish you had shared the whole project from the beginning !! However, if you present the data you have and the way you need it to be represented (in stages) we will get to where we want to be ! The problem is that we may have gone down the wrong road in the first place. Try to explain the whole project: What is it that you are trying to do. Then explain the data you have. Then....and only then, do we start to design a way to achieve it.

 

[Pettor]

Good morning Mistrellis and thank you very much for your help and time,

Sorry for the inconvenience. I have tried in different threads to find someone to guide me through the whole project but it wasn't possible and I had to figure out myself a way to put the things together.

Let me present you what I have in mind to do.

I am gathering, from a website, the results of football games and keeping them in an excel file. The data have the below structure (this is a simplified form as I keep also data based on some functions at a second stage).

DATE	HOME_TEAM	AWAY_TEAM	HALF_TIME_RESULT	FULL_TIME_RESULT
01/01/16	LAZIO	ROMA	1:1	2:1

<tbody>
</tbody>

At the second stage, I am using some functions to get data based on the result of the game (i.e. number of goals per half, number of goals per team e.t.c.). So, a new sheet is created with the records like below:

DATE	HOME_TEAM	AWAY_TEAM	HALF_TIME_RESULT	FULL_TIME_RESULT	HOME_TEAM_GOALS	AWAY_TEAM_GOALS	UNDER_OVER_2,5GOALS
01/01/16	LAZIO	ROMA	1:1	2:1	2	1	OVER 2,5

<tbody>
</tbody>

and so on... for all the data I am interested. The complete file includes around 60 columns in total.

Until now I was using filters and a SUBTOTAL function for each column in Excel and I was getting the percentage for each team and outcome, but as the file gets bigger and more complicated, I am trying to go to a more flexible system like Access.

The problem is I don't know much from databases and what I have read so far is not sufficient to design a database in an effective way.

What I want now to achieve is, when I am choosing two teams that are going to play a game in the future (or one team so as to keep it easier for our design model), to have their past statistical data presented in a record, column by column. I had in mind something like the below table as an outcome.

HOME_TEAM	TOTAL_GAMES	NO_OF_SCORED_GOALS	NO_OF_GOALS_CONC	UNDER_2,5 GOALS	OVER_2,5_GOALS	BOTH_TEAM_SCORED
LAZIO	10	50	30	5	10	5

<tbody>
</tbody>

and so on... for around 60 columns with interesting data.

Concerning the volume of data, the total number of teams could be a couple of thousands and the total number of leagues could be a couple of hundreds (because a team can play league, cup or international games) and all these are also related with the parameter date.

What basically I would like to know is the methodology that I have to use to have this analysis of the data presented in columns. For example, is it possible with one query to get the data in every column related to a team or do I have to create one query for each column and combine them all together in another way?

I tried to write a query in order to get the data, but the WHERE clause can be used only for one value at a time (i.e. when the final result is Home Win) and as you can figure out this didn't work as each column contains different types of data. Some will present the goals, some others will present the result e.t.c.

I don' know if this can be achieved, due to my lack of knowledge, but I would like to give it a try.

Please let me know if I made myself clear or you need more details about it.

 

[mistrellis]

Ok that's great. This will take a while so please be patient.
Just one quick question:

When you have two teams to compare like so:
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]HOME_TEAM
[/TD]
[TD]TOTAL_GAMES
[/TD]
[TD]NO_OF_SCORED_GOALS
[/TD]
[TD]NO_OF_GOALS_CONC
[/TD]
[TD]UNDER_2,5 GOALS
[/TD]
[TD]OVER_2,5_GOALS
[/TD]
[TD]BOTH_TEAM_SCORED
[/TD]
[/TR]
[TR]
[TD]LAZIO
ROMA[/TD]
[TD]10
11[/TD]
[TD]50
47[/TD]
[TD]30
24[/TD]
[TD]5
4[/TD]
[TD]10
6[/TD]
[TD]5
4[/TD]
[/TR]
</tbody>[/TABLE]
I am assuming that you want to create a predictive formula. It would be good to incorporate this so that when you enter your teams, the database gives you the predictions based on your data. Indeed you can link to a database that automatically imports fixture lists and like that to your data. This might still be achievable in Excel but I think we may have to utilise the benefits of both.

Stage One will be to generate the results into the redesigned query using formulas (to extract the goals from the score data and also create an if formula for the above 2.5) then we can group them.

I am only available during the week so bear with me.