Solved

# IF statement and running total

Posted on 2003-11-16
504 Views
Hello,

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.

WarCrimes
0
Question by:Cory Vandenberg
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 5

LVL 5

Expert Comment

ID: 9761095
Hi,

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;

Peter
0

LVL 18

Author Comment

ID: 9761197
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
GROUP BY Teams.TeamID

WarCrimes
0

LVL 18

Author Comment

ID: 9761205
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.

WC
0

LVL 5

Expert Comment

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

HomeID     VisitorID    Result
---------     ----------    -------
?                   ?                 ?            (home win)
?                   ?              TIE            tie
?                   ?                 ?            (visitor win)

Peter
0

LVL 5

Expert Comment

ID: 9761222
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;
0

LVL 18

Author Comment

ID: 9761250
I tried yours and I get an error in the expression statement that the object doesn't contain the Automation object 'ASI.
0

LVL 5

Accepted Solution

peterpuscas earned 200 total points
ID: 9761373
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;

than

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;
0

LVL 18

Author Comment

ID: 9761420
Peter,

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.

SELECT TeamID,
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,
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?

WC
0

LVL 5

Expert Comment

ID: 9763631
>>> 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:

Peter
0

LVL 18

Author Comment

ID: 9767016
Thanks for all your help Peter.

WC
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month6 days, 2 hours left to enroll