IF statement and running total


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.

LVL 18
Cory VandenbergSenior Risk ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I'm not very sure about the Result values.
This is my assumption:
Home win :          Result = "W"
Tie           :          Result = "TIE"
Home Losse:         Result = "L"
Game not played:   Result = NULL

SELECT teams.teamID AS teamID,
         dcount("[HomeID]", "Schedule", "("&[TeamID]&" = [HomeID]) and (Result = 'W')" ) AS homeWins,
         dcount("[HomeID]", "Schedule", "("&[TeamID]&" = [HomeID]) and (Result = 'TIE')" ) AS homeTies,
         dcount("[HomeID]", "Schedule", "("&[TeamID]&" = [HomeID]) and (Result = 'L')" ) AS homeLoses
         FROM teams;

Cory VandenbergSenior Risk ManagerAuthor Commented:
I have been tinkering with this.  I have found a solution that seems to work for counting a team's HomeWins, but I would like to know if there is a better way to do it.

SELECT Teams.TeamID, COUNT(*) AS HomeWins FROM Teams, Schedule
WHERE TeamID = HomeID AND HomeID = Result

Cory VandenbergSenior Risk ManagerAuthor Commented:
Hi Peter,

I used combo boxes for the values of HomeID, VisitorID, Result columns that take their values from my Teams table.  I also allowed other entries so that "TIE" can be entered into the Result column if a team isn't chosen as the winner.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Did you tried my SQL?
Could you please give an example for Schedule table values?

HomeID     VisitorID    Result    
---------     ----------    -------
?                   ?                 ?            (home win)
?                   ?              TIE            tie
?                   ?                 ?            (visitor win)  
I didn't read your last message, this should be the solution for you:

SELECT teams.teamID AS teamID,
         dcount("[HomeID]", "Schedule", "("&[TeamID]&" = [HomeID]) and (Result = HomeID)" ) AS homeWins,
         dcount("[HomeID]", "Schedule", "("&[TeamID]&" = [HomeID]) and (Result = 'TIE')" ) AS homeTies,
         dcount("[HomeID]", "Schedule", "("&[TeamID]&" = [HomeID]) and (Result = VisitorID)" ) AS homeLoses
         FROM teams;
Cory VandenbergSenior Risk ManagerAuthor Commented:
I tried yours and I get an error in the expression statement that the object doesn't contain the Automation object 'ASI.
I tested for numerical IDs before, your fields are probably strings.

Try this first to see if DCount is working for you:

SELECT dcount("[HomeID]", "Schedule" ) AS homeWins FROM teams;


SELECT teamID, dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [HomeID]) and (Result = HomeID)" ) AS homeWins, dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [HomeID]) and (Result = 'TIE')" ) AS homeTies, dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [HomeID]) and (Result = VisitorID)" ) AS homeLoses
FROM teams;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cory VandenbergSenior Risk ManagerAuthor Commented:

Very nice.  That works.  I notice the extra ' ' in the dcount statement in the 3rd parameter.  Could you explain why it is '"&[TeamID]&"' instead of just [TeamID].

I've also expanded this to calculate Road Record.  Now I want to add Home-Road records to get Total Record.  This is what I tried to do.

     dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [HomeID]) and (Result = HomeID)" ) AS HomeWins,
     dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [HomeID]) and (Result = VisitorID)" ) AS HomeLosses,
     dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [HomeID]) and (Result = 'TIE')" ) AS HomeTies,
     dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [VisitorID]) and (Result = VisitorID)" ) AS RoadWins,
     dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [VisitorID]) and (Result = HomeID)" ) AS RoadLosses,
     dcount("[HomeID]", "Schedule", "('"&[TeamID]&"' = [VisitorID]) and (Result = 'TIE')" ) AS RoadTies,
     (HomeWins + RoadWins) AS TotalWins,
     (HomeLosses + RoadLosses) AS TotalLosses,
     (HomeTies + RoadTies) AS TotalTies
FROM Teams;

The problem is that it is simply concatinating the values in the columns for HomeWins and RoadWins, etc.   I guess it's treating them as strings instead of numbers.  So instead of 4+2=6, I get 4+2=42.  Do I just need to do more dcounts with different conditionals or is there a simpler way around this?

>>> Could you explain why it is '"&[TeamID]&"' instead of just [TeamID].
Any field in the 3th param should be a field of the second param,
so we have to use the value of [TeamID] which is a field from outside,
from the SQL statement in our case.
Extra ' '  are to evaluate [TeamID] like a string
ex: for teamID="1"
"('"  &  [TeamID]  &   "' = [HomeID]) and ([Result] = [HomeID])"
will be evaluated as
"('"  & "1"& " ' = [HomeID]) and ([Result] = [HomeID])"
"('1' = [HomeID]) and ([Result] = [HomeID])"
Without ' ' would be (1=[HomeID]) which returns an error because 1 is integer and [HomeID] is String.

use CINT() for totals:
     (cint(HomeWins) + cint(RoadWins)) AS TotalWins,
     (cint(HomeLosses) + cint(RoadLosses)) AS TotalLosses,
     (cint(HomeTies) + cint(RoadTies)) AS TotalTies

Cory VandenbergSenior Risk ManagerAuthor Commented:
Thanks for all your help Peter.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.