SQL query help

Barti

Hi there, I'm having a problem with one of my queries. I'm using MS Access 2007 and I have a query which asks me to:
'Get the total points scored against a team supplied by the user'.

My table is as follows after joining two separate tables (TEAM and TEAMPERMATCH):



To create this, I used the following SQL query:

SELECT *
FROM TEAM as T INNER JOIN TEAMPERMATCH as TPM on T.TeamID=TPM.TeamID;

What I want to do from here is get the sum of all the points scored against a team whose name the
user supplies on request of the query. I know what I want to do but I can't seem to form a query around it.
What I think I am meant to do is select all the matches which the team supplied played in and then get
all the other teams that played these same matches and sum their scores. I just can't figure out how to write it in a query.

I don't know if it matters or not but the end aim is to use this query through a Visual Basic program to fill a textbox
for the total score against a selected team. I have other queries working through VB though so I'm sure it won't be too difficult
to implemet again.

Hope someone can help me out

Sparks

SELECT SUM(TeamScore) AS TotalPointsScoredAgainst from TEAM WHERE TEAM.MatchID IN (SELECT MatchID FROM TEAM WHERE TeamName = $UserInput ) AND TEAM.TeamName <> $UserInput

It's not the best because it's written as a subquery and not as sets but subquery->set mapping isn't something I do well after midnight

Barti

Cheers Sparks, that's exactly what I needed! I was clueless as to how to translate my thoughts into a query but that's brilliant now