Count instances of a certain value

B

Brandon

I am trying to make a query which will count the number of instances that a
specific text string comes up in a given month.

For example, for the [Species] field, I want to make a query which will have
several fields [House Finch] [House Sparrow] [Song Sparrow] and what have
you, and for that field, report the number of times each comes up in a given
month.

I've had quite a bit of trouble making this work, I attempted to use a
Dcount to no avail.

Much thanks for your help,

Brandon
 
J

Jeff Boyce

Brandon

?You have fields labeled [House Finch], [House Sparrow], [Song Sparrow]?
Does this mean every time you decide a new bird needs tracking, you have to
add a new field, modify all your queries, forms and reports, and review all
your procedures/modules?!

If you use "repeating fields" (e.g., [BirdType1], [BirdType2], ...), you
have a ... spreadsheet!

A relational database uses a table to list all Species (one row per, one
field only, plus an ID field). Then another table is used to refer to
whatever you are tracking about a BirdType, using the SpeciesID (which
points back to the Species table).

You may need to look into "normalization" to get your data organized in a
way that let's you use the features and functions that Access provides.
Otherwise, you could consider using a spreadsheet, if you don't need a
relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
B

Brandon

Jeff,

No, i do not have fields labeled by each bird species. I have a single field
in each database entry [Species]. Each entry has the unique Lab Number,
Locality (Where found), Date collected, Species, Recapture, and then Results.

I am trying to make a query (which will eventually lead to a report) which
will allow me to see a summary of a given month which will display the number
of birds this month, the number of recaptures, and the number positive in the
"results" field. In additon to this "current" information, I would like to
have YTD (previous info not including the current month). This information
will be on several bird species, any others which do not fall into the
largest of the groups (always is house finch , house sparrow etc etc) is
lumped into "Other"

You see, I originally attempted to make something like this work where it
sorted counted species using an Iif which worked well until the YTD issue. I
had two queries which I joined, one for YTD one for Current month. However,
in the situation where there was Information for say, House finch, in the
current month but not in the YTD, the YTD query would proppagate a null and
thus the joined fields for (like my previous example) house finsh, would not
be included since there as not a value in the YTD.

After looking at it, the way I was doing it is an overall better idea, I
just was having issues with the joins and propagations of nulls.

Any ideas as to how I could go about this, either something related to one
my my attempts or something new alltogether?

Sincerely and Much Appreciative,

Brandon
 
B

Brandon

After Reviewing what I said and some of my old work, I rewrote it and cleaned
it up some. If I can get this single query to work the proper way, I will be
able to do the rest for myself.

SELECT [Capture Summary].Month, [Capture Summary].Year, [Capture
Summary].Species, Count([Capture Summary].[Lab Number]) AS [CountOfLab
Number], Sum([Capture Summary].Recap) AS SumOfRecap, Sum([Capture
Summary].[SLE Value]) AS [SumOfSLE Value], Sum([Capture Summary].[WEE Value])
AS [SumOfWEE Value], Sum([Capture Summary].[WNV Value]) AS [SumOfWNV Value]
FROM [Capture Summary]
GROUP BY [Capture Summary].Month, [Capture Summary].Year, [Capture
Summary].Species;

That is the actual SQL. It's pretty simple at the moment, it just shows the
entire database and lists the information for each species for the month.
However, what I would like to do, is 'force' it to put a field for species
and 0s in the rest of the fields for certain species even if there are none
of that species captured in a given month. If you need to know the species
themselves, they are: House Finch, House Sparrow, White-Crowned Sparrow, Song
Sparrow, Crow, and Other Species.

In a previous query, it looks up the fields of interest and has a calculated
field which has an IIf statement which will return House Sparrow...et al, and
Other Species if it is not in the list.

Thanks again,

Brandon
 
J

Jeff Boyce

Brandon

Have you looked into the Nz() function to "force" a zero?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
B

Brandon

Hm, the Nz() function looks like it might be doing the sort of thing which I
need. My one question, however, is what the appropriate place to put it would
be, given my SQL in the previous post.
 
J

Jeff Boyce

Brandon

The Nz() function can be used to force a "missing" (i.e., Null) value to 0,
so you can do math with it. If you are trying to "count" something, you
probably want to surround that something with the Nz(), just in case there's
a Null.

In Access, Nulls "propogate" -- if a Null used as part of a calculation, it
forces the result to become Null (unknown, unknowable).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top