IF statement and running total
Posted on 2003-11-16
I have two tables:
Table - Teams
column - Team ID:
Table - Schedule
column - HomeID:
column - VisitorID:
column - Result:
I am new to using Access and SQL and it's been hard finding tutorials and examples of things I want to do. I have had enough programming background to figure things out a bit, but the first steps are always the hardest.
Basically, what I want is a query to calculate a teams record. So something like this:
SELECT Teams.TeamID, something AS numHomeWins, something AS numHomeLosses, something AS numHomeTies, ....
I'm also calculating Road record and Total records, but if I could be supplied with the code for Home record, I'd like to think I could figure the rest out on my own.
Basically, the query needs to check if the Result column is NULL. If it's not then each time Teams.TeamID = Schedule.HomeID it needs to check if HomeID = Result. If it does then it needs to add 1 to the numHomeWins for that team, else if Result = "TIE" then add 1 to numHomeTies, else add 1 to numHomeLosses.
Would it be easier to do this another way, like with a form or using VBA to code a solution? I'm always looking for the most efficient way to do things while following good design. Thank you ahead of time for your help.