SIMPLE QUESTION MAYBE? need to count records BUT only records that

[Keith]

not have the data "non-rev" in in the field.

Two table DB it counts and tracks TRAINS.

first table has.... Date / Train Line i.e. (Noth south east west) /
Station i.e. N-1 through N-12 (north station #1 through 12 east 1-6 west
etc...) and also a "NON_REV" designation. / auto record #

2nd table has VEH#/ Route#/ Var/ BL#/ Destination / # of passengers / Time
/record #

I need to COUNT the total # of stations that DO NOT have "non-rev" as the
data. Those are non rev trains and do not count as a trip.

What I am after is

1. total passengers (i did this "desidn view" (SUM) this works fine)
2 total "trips" this comes from "(count) of records this also works fine
3 This is what I can NOT do ...Count all STATIONS with any data "BUT" the
"NON-REV" entry.

example: Stations
N-1
W-5
N-12
E-2
NON-REV
s-4

the above should count up as 5 paid trips.

The sql created in design view for my two working sum and count is here.

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 #];

I have been offered code but this query already has SQL written for two
other simple total counts shown above. I do not know how to splice the
offered code into the existing SQL code above created by the design view.
people are offering is two options for code I can get neither to work even if
I start a fresh blank new query!!!


Please help I have been on this one thing all day with ZERO progerss....
Keith

 

[golfinray]

Have you tried in your query criteria Not Like "non-rev"?

 

[ken]

Try this:

SELECT
SUM(Pass) AS TotalPass,
COUNT(*) AS Trips,
SUM(IIF(Stations="NON-REV",0,1)) AS TotalNonRev
FROM [Passenger Check Inbound] INNER JOIN PassSub
ON [Passenger Check Inbound].[Record #] = PassSub.[Record #];

The IIF function returns 0 if the value of Station is "NON-REV",
otherwise it returns 1. The SUM of these values is the same as
counting all rows where Station does not equal "NON-REV" as summing
the 1s is the same as counting those rows.

BTW if you simply want to count all rows, as I assume you do for
Trips, you can use COUNT(*) rather than specifying a column. You only
need to specify a column if you want to exclude Nulls from the count.
You have to do it in SQL view, though, not in design view.

Ken Sheridan
Stafford, England