Hello everyone, I am currently trying to write an ASP baseball stats 

program which utilizes 2 tables in an Access database.  One table has the 

names of the players on the team, and the other has the pitching stats for 

the players.  I have 2 boolean (check box) fields for Wins and Losses.  

I'd like to create either an INNER JOIN or a simple count SQL statement 

that will give me a count of the number of times that the "Win" checkbox 

is true in one field and another field to tell me when the "Loss" field is 

true.  First off, I don't even know if this can be done.  I've tried to 

make an SQL statement just in Access itself, and I can't figure out how to 

do it.  Does anyone know how to give a count of each field's true values 

separately?



Chris

Christopher,



Firstly, you say you have *two* tables, and *two* fields. Which table(s) are

the field(s) in?

Secondly, you say you want to create an INNER JOIN, -or- a COUNT() - which

one do you want? They are not mutually exclusive...

Thirdly, you have checkboxes for "wins" and "losses" - isn't a "loss" an

opposite of a win? or can you both win and lose? If the former, then you

only need *1* field - remember your fields are your attributes. Whether you

win or loss is a value that an attribute can have - they are not attributes

in of themselves...



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Christopher Cote" <chrscote@9...>

Subject: [access_asp] Question about SQL Statement for 2 boolean fields





: Hello everyone, I am currently trying to write an ASP baseball stats

: program which utilizes 2 tables in an Access database.  One table has the

: names of the players on the team, and the other has the pitching stats for

: the players.  I have 2 boolean (check box) fields for Wins and Losses.

: I'd like to create either an INNER JOIN or a simple count SQL statement

: that will give me a count of the number of times that the "Win" checkbox

: is true in one field and another field to tell me when the "Loss" field is

: true.  First off, I don't even know if this can be done.  I've tried to

: make an SQL statement just in Access itself, and I can't figure out how to

: do it.  Does anyone know how to give a count of each field's true values

: separately?



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi folks,

  Sorry for the confusion.  Anyway, I have a table with names of everyone 

on a team. I then have a table which has each pitcher's stats for each 

game.  The pitcher can either have a win, loss, or no decision (for those 

who don't follow baseball, if a pitcher is not involved in the win/loss, 

then he isn't credited with either).  What I was trying to do was an inner 

join between these 2 tables where I have an SQL statement like:



 SELECT Stats.Name, SUM(Field1) as Sums1, SUM(Field2) as Sums2,...,Count

(Wins=True), Count(Losses=True), Count(Saves=True) INNER JOIN Players on 

Players.Name



By doing this, I would end up with a recordset in which I would have the 

sums for each player who has pitched.  What I am trying to figure out is 

how to do the Count Statements.



Chris

Try SUM(iif(win,1,0)) and SUM(iif(loss,1,0) for wins and losses.

This will assign a 1 to each field thats a win, so if you sum them, you get your answer.



> Hello everyone, I am currently trying to write an ASP baseball stats 

> program which utilizes 2 tables in an Access database.  One table has the 

> names of the players on the team, and the other has the pitching stats 

for 

> the players.  I have 2 boolean (check box) fields for Wins and Losses.  

> I'd like to create either an INNER JOIN or a simple count SQL statement 

> that will give me a count of the number of times that the "Win" checkbox 

> is true in one field and another field to tell me when the "Loss" field 

is 

> true.  First off, I don't even know if this can be done.  I've tried to 

> make an SQL statement just in Access itself, and I can't figure out how 

to 

> do it.  Does anyone know how to give a count of each field's true values 

> separately?

> 

> Chris

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Christopher Cote" <chrscote@9...>

Subject: [access_asp] Re: Question about SQL Statement for 2 boolean fields





: Hi folks,

:   Sorry for the confusion.  Anyway, I have a table with names of everyone

: on a team. I then have a table which has each pitcher's stats for each

: game.  The pitcher can either have a win, loss, or no decision (for those

: who don't follow baseball, if a pitcher is not involved in the win/loss,

: then he isn't credited with either).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Then perhaps you need a tristate field:



NULL = no decision

1 = win

0 = loss



then you still have only one field...remember, your fields need to reflect

the *attributes* of the entities. A book on DB modelling/design would help

here. The whole SQL language is predicated upon having properly modelled

databases...when you start deviating, your SQL becomes correspondingly

messier.



Cheers

Ken