God am I dumb!!! Simple question on "count"

[Keith]

I have a field in a table that has 30 diff entries i.e. LAX through lets
say ATL I want to count all of them but the NON_REV fields..

Example:

1.) ATL
2.) LAX
3.) BOS
4.) NON_REV

This should return a count of 3 I tried count "not NON_REV" I dont know how???

 

[fredg]

I have a field in a table that has 30 diff entries i.e. LAX through lets
say ATL I want to count all of them but the NON_REV fields..

Example:

1.) ATL
2.) LAX
3.) BOS
4.) NON_REV

This should return a count of 3 I tried count "not NON_REV" I dont know how???

=Sum(If([FieldName]="Non_Rev",0,1))

 

[Dirk Goldgar]

I have a field in a table that has 30 diff entries i.e. LAX through lets
say ATL I want to count all of them but the NON_REV fields..

Example:

1.) ATL
2.) LAX
3.) BOS
4.) NON_REV

This should return a count of 3 I tried count "not NON_REV" I dont know
how???

Use Sum on a calculated value:

Sum(IIf([YourField]='NON_REV', 0, 1)

 

[Jellifish]

SELECT COUNT(MyField)
FROM MyTable
WHERE MyField <> "NON_REV"

 

[Keith]

I am in query design view I tried to put this into criteria and ger errors
missing ([ etc... Where do I put this command I tried sql wiew bit I already
have code there and daaind it to the end causes other errors. I their a
simple way for dumb a's like me to do this simple task....The code is grate
but where to put it?

I have a field in a table that has 30 diff entries i.e. LAX through lets
say ATL I want to count all of them but the NON_REV fields..

Example:

1.) ATL
2.) LAX
3.) BOS
4.) NON_REV

This should return a count of 3 I tried count "not NON_REV" I dont know
how???

Use Sum on a calculated value:

Sum(IIf([YourField]='NON_REV', 0, 1)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Keith]

I am in query design view I tried to put this into criteria and ger errors
missing ([ etc... Where do I put this command I tried sql wiew bit I already
have code there and daaind it to the end causes other errors. I their a
simple way for dumb a's like me to do this simple task....The code is grate
but where to put it?

I have a field in a table that has 30 diff entries i.e. LAX through lets
say ATL I want to count all of them but the NON_REV fields..

Example:

1.) ATL
2.) LAX
3.) BOS
4.) NON_REV

This should return a count of 3 I tried count "not NON_REV" I dont know how???

=Sum(If([FieldName]="Non_Rev",0,1))

 

[Keith]

Where do I put this code,I have other working functions in this query I do
not know how to splice this into already exsisting code. When I past it to
the end of the sqlk I ger many errors......Is there a place in "query design
view" to put this code? I tried to put under "criteria" but again many errors
like missing ([ statements.... Do I set the "totals" too count or sum or ???
Going NUTZ
Thx
Keith

 

[Dirk Goldgar]

I am in query design view I tried to put this into criteria and ger errors
missing ([ etc... Where do I put this command I tried sql wiew bit I
already
have code there and daaind it to the end causes other errors. I their a
simple way for dumb a's like me to do this simple task....The code is
grate
but where to put it?

You wouldn't put it into criteria. It's a calculated field, so you would
put it in the field grid, as a new "field". You would probably give it a
name, rather than letting Access assign one, so you would enter something
like this:

RevCount: Sum(IIf([YourField]='NON_REV', 0, 1)

I can't see the query you are building, so I don't know what other fields
you might be extracting or totalling. It's a bit easier to give an example
in SQL. If you built a simple query just to compute the above value from a
table named "YourTable", its SQL view would look like this:

SELECT Sum(IIf([YourField]='NON_REV', 0, 1) As RevCount
FROM YourTable;

Note that the SQL posted by Jellifish, though different (using a restricted
COUNT), would also work for this purpose. But if you want, say, the total
number of records, the number of NON_REV records, and the number of other
("Rev"?) records, you would have to do something like this:

SELECT
Count(*) As TotalRecords,
Sum(IIf([YourField]='NON_REV', 0, 1) As RevCount
Sum(IIf([YourField]='NON_REV', 1, 0) As NonRevCount
FROM YourTable;

 

[Keith]

Thanks so much for your time, I know this is dumb but I am just overloaded,
below is the sql of my query, I pasted the last part from your code but it
gives all kinds of errors. As you will see I have 3 colums 1.) (SUM) theTotal
# of passengers, 2.) (COUNT)Total # of records= TRIPS, 3.) (COUNT) Total # of
stations= TRIPS LESS any with (NON_REV) as the station.

SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS,
Count([Passenger Check Inbound].STATION) AS TRIPS_LESS_NON_REV
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #] RevCount:
Sum(IIf([YourField]='NON_REV', 0, 1);

Keith

I am in query design view I tried to put this into criteria and ger errors
missing ([ etc... Where do I put this command I tried sql wiew bit I
already
have code there and daaind it to the end causes other errors. I their a
simple way for dumb a's like me to do this simple task....The code is
grate
but where to put it?

You wouldn't put it into criteria. It's a calculated field, so you would
put it in the field grid, as a new "field". You would probably give it a
name, rather than letting Access assign one, so you would enter something
like this:

RevCount: Sum(IIf([YourField]='NON_REV', 0, 1)

I can't see the query you are building, so I don't know what other fields
you might be extracting or totalling. It's a bit easier to give an example
in SQL. If you built a simple query just to compute the above value from a
table named "YourTable", its SQL view would look like this:

SELECT Sum(IIf([YourField]='NON_REV', 0, 1) As RevCount
FROM YourTable;

Note that the SQL posted by Jellifish, though different (using a restricted
COUNT), would also work for this purpose. But if you want, say, the total
number of records, the number of NON_REV records, and the number of other
("Rev"?) records, you would have to do something like this:

SELECT
Count(*) As TotalRecords,
Sum(IIf([YourField]='NON_REV', 0, 1) As RevCount
Sum(IIf([YourField]='NON_REV', 1, 0) As NonRevCount
FROM YourTable;


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John W. Vinson]

Thanks so much for your time, I know this is dumb but I am just overloaded,
below is the sql of my query, I pasted the last part from your code but it
gives all kinds of errors. As you will see I have 3 colums 1.) (SUM) theTotal
# of passengers, 2.) (COUNT)Total # of records= TRIPS, 3.) (COUNT) Total # of
stations= TRIPS LESS any with (NON_REV) as the station.

You need FOUR COLUMNS not three.

Try

SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS,
Count([Passenger Check Inbound].STATION) AS TRIPS_LESS_NON_REV,
Sum(IIf([YourField]='NON_REV', 0, 1) AS NON_REV
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #];

Youre TRIPS_LESS_NON_REV will probably return *all* trips, not just the
NON_REV trips since the Count() returns a count of records with a non-null
value in the field being counted; you may want instead to use


SELECT Sum(PassSub.Pass) AS TOTALPASS, Count(PassSub.[Record #]) AS TRIPS,
Sum(IIf([YourField]='NON_REV', 0, 1) AS TRIPS_LESS_NON_REV,
Sum(IIf([YourField]='NON_REV', 0, 1) AS NON_REV
FROM [Passenger Check Inbound] INNER JOIN PassSub ON [Passenger Check
Inbound].[Record #] = PassSub.[Record #];

 

[Fred]

Keith,

To use jellifish's response, you would make that the entire query = the
entire sql. Take some existing query, erase the whole SQL and paste his in.