thanks Duane - that has enabled me to move forward - much appreciated.
On Thursday, 20 July 2017, 22:36, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Phil,
First create a normalized results query so there is a record for each team in each game with the scores and a Win column:
=== quniMatches ===
SELECT Matches.ID, Matches.MatchDate, Matches.Div, Matches.Season,
Matches.HomeTeam AS Team, "H" AS HomeAway, Matches.[Home Goals] AS GoalsFor,
Matches.[Away Goals] AS GoalsAgainst, [Home Goals]>[Away Goals] AS Win
FROM Matches
UNION ALL
SELECT Matches.ID, Matches.MatchDate, Matches.Div, Matches.Season,
Matches.AwayTeam AS Team, "A" AS HomeAway, Matches.[Away Goals] AS GoalsFor,
Matches.[Home Goals] AS GoalsAgainst, [Away Goals]>[Home Goals] AS Win
FROM Matches;
Then create a query to return only the most recent 5 games:
SELECT quniMatches.ID, quniMatches.Div, quniMatches.Season, quniMatches.Team, quniMatches.MatchDate, quniMatches.HomeAway, quniMatches.GoalsFor, quniMatches.GoalsAgainst, quniMatches.Win
FROM quniMatches
WHERE (((quniMatches.ID) In (SELECT TOP 5 ID FROM quniMatches M WHERE M.Team = [quniMatches].Team ORDER BY M.MatchDate DESC )))
ORDER BY quniMatches.Team, quniMatches.MatchDate;
Try to take it from there and come back with questions.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Phil Knowles pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, July 20, 2017 2:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] N from M
Sent: Thursday, July 20, 2017 2:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] N from M
the table is something like this
id
matchdate
div
season
hometeam
awayteam
result
home goals
away goals
etc
it is the sorting out of the last 5 games for each team that is giving me the difficulty - I have been trying select top 5 but how do you do that for each team?
On Thursday, 20 July 2017, 20:12, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Phil,
How about providing the actual table and relevant field names?
I would break this (and all other complex tasks) down into smaller, solvable chunks. I think the first would be to create a query that returns the most recent 5 matches for each team. You could then determine how many of these were wins.
Regards,
Duane Hookom
MN
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Phil Knowles pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, July 20, 2017 1:33 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] N from M
Sent: Thursday, July 20, 2017 1:33 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] N from M
the matches table has lots of fields but yes
matchdate and result are the relevant fields
On Thursday, 20 July 2017, 19:21, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Phil-
What is the full structure of your Matches table? I assume there's an indicator of the winner and the date of the match.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
| www.viescas.com If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book recommendations and all the cool links! |
| www.viescas.com If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book recommendations and all the cool links! |
(Paris, France)
On Jul 20, 2017, at 8:11 PM, Phil Knowles pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
That is all the teams who have achieved 2 wins out of the last 5 matches rather than just for one team.
On Thursday, 20 July 2017, 19:08, "pdk444444@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
I want to be able to select records for a team from my MATCHES table where the team has won say 2 out of the last 5.
What is the best way to do this please?
Phil
__._,_.___
Posted by: Phil Knowles <pdk444444@yahoo.co.uk>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar