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

K

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

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

Keith

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

ErezM via AccessMonster.com said:
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....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top