Question Read

[Ronan]

Edit: first of all, sorry for the terrible thread heading. I meant to add it after I finish the main part, but forgot. Now I cant edit it

Hi, im new to this forum and Access. Im trying to create a database of football (soccer) results, data from which could then be used to provide various information such as league tables and current form.
After much tests and tries I managed to produce a query which would calculate games played/won/drew/lost, goals scored/conceded, etc for each team, and then created a report to display the table.

However, Im struggling with the "current form" feature. I would like to calculate the wins/draws/loses, goals scored/conceded, points, etc for each team over the past:
a) 6 home matches
b) 6 away matches
c) 6 matches (overall; home+away)
Each match has a field called date, which I believe should be used here in determining the last x matches. But, unfortunatelly, I have no idea where to start.

Any help is much appreciated

P.S. I've attached the database I have so far.

 

[Rabbie]

Firstly "date" is a reserved word in Access and so shouldn't be used as a field name. Use something like "matchdate" instead.

Read up on queries in Access Help paying particular attention to Predicates like TOP etc. This will help you get the data for the last 6 home matches or last 6 away matches etc.

When you have done that post any specific questions you may have and I am sure you get answers

 

[Ronan]

Will do. Thanx Rabbie

 

[Ronan]

Ok, I managed to extract the last 6 games (either home, away or both), but the thing that bugs me is I had to specify the teams name to do so. For example, I used...

SELECT TOP 6 tblMatches.dtmDate, tblMatches.txtHomeTeam, tblMatches.txtAwayTeam, tblMatches.intHomeGoals, tblMatches.intAwayGoals
FROM tblMatches
WHERE (((tblMatches.txtHomeTeam)="Arsenal")) OR (((tblMatches.txtAwayTeam)="Arsenal"))
ORDER BY tblMatches.dtmDate DESC;

...to display the last 6 matches for Arsenal (either home or away). Does this mean I would have to create 19 more queries with similar code for the other teams?
Im sure there's an easier way to do it, is there?

P.S. If an admin/mod comes across this topic, I would appreciate if you change the title to something more descriptive as "Football Database Help" for example. Thanx in advance

 

[neileg]

Use a form to capture the team name. A combo box will allow the user to select the team name without having to type itin (and spell it correctly).

 

[Ronan]

I dont quite understand what you mean.
The thing that im looking to do now is display the latest 6 matches of each team and produce a report (such as the one I have for FULL tables - in the db attatched).
For example,

...this screenshot was taken from Soccerway.com and displays the standings of all teams according to their recent form (6 games).
I wanted the report to be updated immediately after a new match was added, discarding the oldest of the recent 6 matches and including the newly added match. This is why I didnt quite get how a form, where I had to enter the name of each team, would work here. Am I missing something?

As I mentioned previously Im quite new to Access and for me the best way to learn something is to combine it with something Im interested in.

 

[neileg]

You said

Does this mean I would have to create 19 more queries with similar code for the other teams?
Im sure there's an easier way to do it, is there?

Yes there is, you only need one query, but change the selection criteria. So I said

Use a form to capture the team name. A combo box will allow the user to select the team name without having to type itin (and spell it correctly).

Your query would look like this (substitute the name of your form and combo box)

SELECT TOP 6 tblMatches.dtmDate, tblMatches.txtHomeTeam, tblMatches.txtAwayTeam, tblMatches.intHomeGoals, tblMatches.intAwayGoals
FROM tblMatches
WHERE (((tblMatches.txtHomeTeam)= Forms!MyFormName!MyComboBoxName)) OR (((tblMatches.txtAwayTeam)=Forms!MyFormName!MyComboBoxName))
ORDER BY tblMatches.dtmDate DESC;