Re: [MS_AccessPros] combining records

 

Phil-

First, you need all the team names in a single column, which you can do with a UNION query:

qryTeamMatches:

SELECT HomeTeam As TeamName 

FROM Matches

UNION ALL

SELECT AwayTeam As TeamName

FROM Matches;

Now just use that in a Totals query:

SELECT TeamName, Count(TeamName) As CountOf Matches

FROM qryTeamMatches

GROUP BY TeamName;

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/ 

(Paris, France)

On Jul 20, 2017, at 4:19 PM, pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Guys

this probably should be really easy but it's been a long day ......

I have a table called MATCHES which has 2 fields called HOMETEAM and AWAYTEAM.

the teams are linked to a table called CLUBS

all I want to do is group all matches (both home and away) for all teams together and lets say for example just count the number of match records for each team.

any help would be appreciated

Phil

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

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.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___