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?

VALUE A	VALUE B	VALUE C
A	B	A
A	B	B
A	B	C
A	B	B
A	B	C
VALUES AB	VALUES CA	VALUES CB	VALUES CC
A	1	2	2
B	1	2	2

<tbody>
</tbody>

 

[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.

Date	TYPE_OF_EVENT	HOME_TEAM	AWAY_TEAM	HALF_TIME_RESULT	FULL_TIME_RESULT	HOME_GOALS	AWAY_GOALS	TOTAL_GOALS	OVER_2pt5
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>
</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:

HOME_TEAM	TOTAL_GAMES	NO_OF_SCORED_GOALS	NO_OF_GOALS_CONC	UNDER_2,5 GOALS	OVER_2,5_GOALS	BOTH_TEAM_SCORED	TYPE_OF_EVENT	SEASON
LAZIO	38	50	24	29	9	15	SERIE A	2014-15
LAZIO	38	53	30	22	16	13	SERIE A	2015-16
LAZIO	6	12	4	4	2	3	CHAMPIONS LEAGUE	2015-16

<tbody>
</tbody>

with a further Summary2 like this:

HOME_TEAM	TOTAL_GAMES	NO_OF_SCORED_GOALS	NO_OF_GOALS_CONC	UNDER_2,5 GOALS	OVER_2,5_GOALS	BOTH_TEAM_SCORED
LAZIO	82	115	58	55	27	31

<tbody>
</tbody>

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