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]

 

[Pettor]

Indeed the total outcome should be something like targeted statistical data. The term predictive is more specialized I think.

What I want mostly, is to have in detail, the percentage of the past outcomes for x number of football market categories (shown in each column) so as to decide if something could worth to take under consideration or not.

A team can behave in a certain pattern and may score more than two goals, let's say, on more games than it is estimated and it could be worth knowing (i.e. 70% of the games are over 2,5 goals).

My main goal is to present these values in the columns, in a form of a percentage of the total played matches for a given period (i.e. season 2015-2016 - So, there are 3 parameters) and ideally the database should load the data with the future games and for every match should give all the details below. (Probably this could be achieved by an Access Report, I am still reading this chapter...). If this is not achievable I could fill the data manually on a form and get the data separately for each game. It depends on what is feasible and easier to be done.

Your effort is very much appreciated I have to say it once again. I can not test the formulas before afternoon, so send me your thoughts to deal in the weekend and we will continue when you are again available. I am not in such a rush.

Have a very nice time and enjoyable Weekend!

 

[Marbles]

It might be easier to post a link to the website's data.

 

[Pettor]

Hi Marbles,


Thank you for your reply. The website data is not the problem. There are 5 columns. Date, Home team, Away team, HT result and FT result.
The manipulation of the data is the problem.

 

[Marbles]

You would be surprised between what you think you're describing the problem, and what we're trying to sort it with. But looking at the actual data makes a big difference.

 

[Micron]

As soon as I saw your first post, I thought "should be doable with a crosstab query". I will be forthcoming in admitting I did not digest much of what came after the suggestion to use five queries and the expansion of the problem (too late at night at this point). However, it may be worth your while to check it out. If you use the crosstab query wizard, there is a limit of how many fields you can use (I think 3) as row headings (or is it column headings)? Whichever, I have bypassed that in the past by opening my crosstab in design view and manually adding more. I don't use crosstabs much so I've never mastered them, but sometimes they save the day. You can always use a ct query as a table to do further calculations, or in your case, as a source for a make table or append query. Might be worth your while to Google about them and see if you think they would do what you want.

 

[Pettor]

Thank you Micron,

I will take a look for sure on crosstab queries. You see, my main problem, and this is what is all about, is that I am so "new" in databases that I don't know how to have a realistic approach to the problem. I tried in different posts to get a guidance for the "big

picture" of the project but it wasn't possible and I am trying to "crosstab" the info in order to get to the result. Ideally I would like to get the percentages, of the data that are registered in columns in a "fixtures master table" for two teams that they will be

chosen in a database form. Or even better the database to present them for all the forthcoming matches. So simple and so difficult at the same time. Mistrellis was the first one that make a serious approach and I am waiting him to guide me for the next

steps. I tried to use the DCount function in a query ONLY for the 3 outcomes (i.e. Win, Draw, Lose) but the MS access almost crushed... Now I am reading about databases and hope to get a serious help so as to achieve the goal. Let's see.

Thanks again all of you dealing with it.

 

[Pettor]

Thanks Marbles,

I will send you later an excel file with all the data gathered in the master table.

 

[Micron]

Based on your comments, I'm thinking the title of your post may be over simplified in that your need goes much deeper than a solution to a query problem. If you're not proficient in relational db design, I'd suggest a "time out" while you look deeper into the subject. You may not yet have the framework you need, or when you do, you may not have it set up correctly. I Googled "ms access football stats database" to see if anyone had already done this, and as usual, I find that I'm not the first person to ever wonder about something (I got 675,000 hits), some of which pertain to NFL, which might also have something to offer. I suggest you invest some time up front to maximize your chances of success. At the very least, you might pick up ideas that you never thought of.

 

[mistrellis]

So to get from this:

1	01/01/16	LAZIO	ROMA	1:1	2:1
2	01/01/16	ROMA	MAN UTD	11:0	11:10
3	01/01/16	STOKE CITY	REAL MADRID	0:0	1:0

<caption> Table1 </caption><thead>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] DATE [/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]

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

to this...

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

<caption> Query1 </caption><thead>
[TH="bgcolor: #c0c0c0"] Date [/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]

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

We need to create a query with the following additional formulas to the existing data:

HOME_GOALS: Val(Left([FULL_TIME_RESULT],InStrRev([FULL_TIME_RESULT],":")-1))

This extracts the home goals as a number (and still works in the unlikely event of someone scoring double figures.

AWAY_GOALS: Val(Mid([FULL_TIME_RESULT],InStrRev([FULL_TIME_RESULT],":")+1))

again the same for the away goals.

TOTAL_GOALS: [HOME_GOALS]+[AWAY_GOALS]

This just adds the two previous formula.

Finally for this query:

OVER_2pt5: IIf([TOTAL_GOALS]>2.5,"Over","Under")

 

[Pettor]

Thank you so much Mistrellis, I will apply your instructions to the database and I will revert. It is a build and learn process.

I usually do this procedure in Excel so as to be easier for me to deal with the information and then I copy all the relevant data to Access. It is just a copy paste process as the functions are already entered in excel.

So all this analysis based on the resullt (no. of goals, under/over e.t.c.) for all the additional columns, is already done in Excel without the need to be done in Access as well.

It is very good to know how to be done automatically in Access and I think this has definitely to be done so as to be a complete tool.

Now, given that we have this data already entered what should be the next step, design wised, so as to start getting the desired outcome?

What I had initially in mind was to create a new table with all the teams ascending by name and have all the summarized data extended in the relevant columns but I realized that there are two critical parameters that have to be considered. The first is the type of event (i.e. league, cup, or international event) that the team participates and the second is the time period which is related to the event.

It is not enough to know the behavior of the team if you can not distinguish if it is about the current season or the previous. It has to be a chosen field and the appeared data have to be linked to this detail, so, I created a new table with the starting and end dates of each event. Do you think it is going to be needed? How many tables do you think that there are going to be needed for this project?