Solved

IF statement and running total

Posted on 2003-11-16
10
504 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…

734 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