Please help...Dumb person help on "count" but with exceptions ?

[Keith]

My questions are not showing up??????

Going NUTS

Have 3 fields one sum works fine. one count works fine need to add another
count but with exception. In english: count all BUT do not count ones with
NON_REV as the data.

People are giving me code but I do not know how to splice it into this:

SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #];

The code people gave me varies

1.) =Sum(If([FieldName]="Non_Rev",0,1)) If this works how do I splice
it into the above ?

2.) SELECT COUNT(MyField)
FROM MyTable
WHERE MyField <> "NON_REV" again if this one is right how do I
splice it into the above? I am in design view when I created the first
two....

Any help Thanks in advance....

 

[ErezM via AccessMonster.com]

to use the first solution, From this:
SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #]

it changes to this
SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS,
Sum(If([FieldName]="Non_Rev",0,1)) As MySmartCout
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #]

just to make sure:
"MySmartCount" is the column name/caption that will appear in the resulting
table
"FieldName" inside the square brackets has to be changed to the actual field
name you are counting

no offence, ha?

My questions are not showing up??????

Going NUTS

Have 3 fields one sum works fine. one count works fine need to add another
count but with exception. In english: count all BUT do not count ones with
NON_REV as the data.

People are giving me code but I do not know how to splice it into this:

SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #];

The code people gave me varies

1.) =Sum(If([FieldName]="Non_Rev",0,1)) If this works how do I splice
it into the above ?

2.) SELECT COUNT(MyField)
FROM MyTable
WHERE MyField <> "NON_REV" again if this one is right how do I
splice it into the above? I am in design view when I created the first
two....

Any help Thanks in advance....

 

[Keith]

Thank you, this really showed me something I can apply in the future

to use the first solution, From this:
SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #]

it changes to this
SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS,
Sum(If([FieldName]="Non_Rev",0,1)) As MySmartCout
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #]

just to make sure:
"MySmartCount" is the column name/caption that will appear in the resulting
table
"FieldName" inside the square brackets has to be changed to the actual field
name you are counting

no offence, ha?

My questions are not showing up??????

Going NUTS

Have 3 fields one sum works fine. one count works fine need to add another
count but with exception. In english: count all BUT do not count ones with
NON_REV as the data.

People are giving me code but I do not know how to splice it into this:

SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #];

The code people gave me varies

1.) =Sum(If([FieldName]="Non_Rev",0,1)) If this works how do I splice
it into the above ?

2.) SELECT COUNT(MyField)
FROM MyTable
WHERE MyField <> "NON_REV" again if this one is right how do I
splice it into the above? I am in design view when I created the first
two....

Any help Thanks in advance....