Date filter in query

[Pettor]

Hello guys,

I would like to create a query that will fill out the column "Goals Average" from a fixture table but I want this to be done only until the date that it is mentioned in the column "Date".
How could I do that?

Many thanks

Date	League	Team	Goals Average
1/1/2020​	Spain	Barcelona	2,5​
1/2/2020​	Spain	Barcelona	2,4​
1/3/2020​	Spain	Barcelona	2,3​

 

[Joe4]

I am not sure I follow. Can you provide more detail?

It might be helpful to show us what your data in your data Table looks like, and then based on that, show us what you want your Query to look like.

 

[Pettor]

Hi Joe,

The table with the fixtures is enormous and it will be difficult to be shared.
For simplicity sake let's say that it has all the matches and the goals scored and conceded.
I am running a query already to get the average goals per team but what I want is for every match to have the average of goals until the date of the match based on column A (Date).
Every time that a match is played the statistics are changing, so I want to have in the query the average of goals scored for the home team until the date of the match.
So instead of getting the data in table A, I receive the data like in table B where the query calculates the latest data and it fills out the column "goals average" with the same values for every line.

Is it more clear now?
A

Date	League	Team	Goals Average
1/1/2020	Spain	Barcelona	2,5
1/2/2020	Spain	Barcelona	2,4
1/3/2020	Spain	Barcelona	2,3

B

Date	League	Team	Goals Average
1/1/2020	Spain	Barcelona	2,3
1/2/2020	Spain	Barcelona	2,3
1/3/2020	Spain	Barcelona	2,3

 

[Joe4]

So, do you want the "Goals Average" to show the current average (all games played up to this point) for every single listing?
If so, what you want to do is an Aggregate (Totals) Query, where you group the records by the Team for every game played, and take the Average of the Goals.
So then you will have a query that has one record per team that has their current Goals Average.
Then create a new query that has your Dates/Teams data table, and join that to the query you just created, joining on "Team".
You can then return all the individual data fields from the data table, and the Average from the query.

 

[Pettor]

Thank you Joe, I will give it a try. Just a quick question because from my understanding (I am totally naive in databases) this method will return the result I already get, placing in every listing (row) the same current average of the same team, although I want to get the average of the team that is mentioned in column "team" but up to the date that is also mentioned in column "date". I may be totally wrong but if let's say, the data table has just 3 columns "date" (all the dates that there was a game, "team" (for simplicity let's have only two teams) and the "number of goals", how would you write it to get the average goals per team up to every date that is mentioned in tha date column? Many thanks

 

[Pettor]

Now that I am thinking it maybe I should create an average query grouping by date and team and then return to the new query the average that matches the date AND the team from the first aggregate query. I will give it a try and I will come back ?
Thanks!