Solved

IF statement and running total

Posted on 2003-11-16
10
490 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

777 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