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]

We now need to start grouping our data in order to create the relevant final report. I have added the type of event as shown.

01/01/16	SERIE A	LAZIO	ROMA	1:1	2:1	2	1	3	Over
01/01/16	CHAMPIONS LEAGUE	ROMA	MAN UTD	11:0	11:10	11	10	21	Over
01/01/16	CHAMPIONS LEAGUE	STOKE CITY	REAL MADRID	0:0	1:0	1	0	1	Under

<tbody>
[TH="bgcolor: #c0c0c0"] Date [/TH]
[TH="bgcolor: #c0c0c0"] TYPE_OF_EVENT [/TH]
[TH="bgcolor: #c0c0c0"] HOME_TEAM [/TH]
[TH="bgcolor: #c0c0c0"] AWAY_TEAM [/TH]
[TH="bgcolor: #c0c0c0"] HALF_TIME_RESULT [/TH]
[TH="bgcolor: #c0c0c0"] FULL_TIME_RESULT [/TH]
[TH="bgcolor: #c0c0c0"] HOME_GOALS [/TH]
[TH="bgcolor: #c0c0c0"] AWAY_GOALS [/TH]
[TH="bgcolor: #c0c0c0"] TOTAL_GOALS [/TH]
[TH="bgcolor: #c0c0c0"] OVER_2pt5 [/TH]

</tbody>

There would be two summaries: The first incorporating the SEASON and TYPE_OF_EVENT and then a more global summary which captures all data.

Summary1 could look like this:
[TABLE="class: grid, width: 1108"]
<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]
[TD]TYPE_OF_EVENT[/TD]
[TD]SEASON[/TD]
[/TR]
[TR]
[TD]LAZIO[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[TD]SERIE A[/TD]
[TD]2014-15[/TD]
[/TR]
[TR]
[TD]LAZIO[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]
[TD]SERIE A[/TD]
[TD]2015-16[/TD]
[/TR]
[TR]
[TD]LAZIO[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]CHAMPIONS LEAGUE[/TD]
[TD]2015-16[/TD]
[/TR]
</tbody>[/TABLE]

with a further Summary2 like this:
[TABLE="class: grid, width: 864, align: left"]
<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[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]31[/TD]
[/TR]
</tbody>[/TABLE]







This is where you need to have an idea of what you actually want in the end. It is important also to note how you are going to present this data. Are you simply going to paste it back (or link it) back to an excel sheet or paper copy / form within Access etc.

If you had an automatic feed (from a website) with the ongoing fixtures you could have an excel file that pulls in that data from a site and vlookup your Access Data to marry the two up.

Do you want to create Summary1 ?

I could with some data to work with (if you have the site or an excel sheet)?

 

[mistrellis]

There is also the concern that these summaries are only capturing the data of the team when it plays at Home. What provision is needed for when the team plays Away?

I am assuming that you need both data sets and when selecting your summary of who is playing who then it pulls in the Home data if the team is selected first and Away data for the second team.

If this is the case then we need to introduce an additional column HOME/AWAY

 

[Pettor]

Hi Mistrellis,

Yes there is a provision for the Home/Away distinction and it is rather easy to get the data as the Home team is always mentioned in the, let's say, A column and the Away always in the B column. Both summary approaches is exactly what I should expect from the system.

 

[mistrellis]

My point is: If the team is in Column A then use the Home data and if in column B then use the AWAY data. At present we only have Home data in Summary1