Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

IF statement and running total

Posted on 2003-11-16
10
Medium Priority
?
526 Views
Last Modified: 2008-03-10
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
Comment
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
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 5

Expert Comment

by:peterpuscas
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

by:Cory Vandenberg
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

by:Cory Vandenberg
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:peterpuscas
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

by:peterpuscas
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

by:Cory Vandenberg
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

by:
peterpuscas earned 800 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

by:Cory Vandenberg
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,
     (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?

WC
0
 
LVL 5

Expert Comment

by:peterpuscas
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:
     (cint(HomeWins) + cint(RoadWins)) AS TotalWins,
     (cint(HomeLosses) + cint(RoadLosses)) AS TotalLosses,
     (cint(HomeTies) + cint(RoadTies)) AS TotalTies

Peter
0
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 9767016
Thanks for all your help Peter.

WC
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question