-----Original Message-----
Hi again,
A bit more detail...
1. My suggestion applies to the QBE grid view rather
than the SQL view of a query.
2. The bin name expressions I suggest would go into
blank columns of the grid; you can call the bin names
before the colon anything you want as long as you don't
use special characters (you get into trouble that way!).
These names will become column headings when you run the
query, but will have no effect on your original data. You
can supply an = sign in front of the iif function name
(the missing operator error you got before), but I don't
think Access requires it in this case and may remove it
anyway.
3. Where I say "size" in the iif statement, you would
say the actual name of the size field name in your table.
If the field name has space(es)in it, you'll have to
enclose the name in square brackets (e.g. [fish size]) to
keep Access happy.
4. The "1,0" part says that if the size value of the
current record falls in the range for that bin, make the
bin value 1 for that record; else make it 0. Only one bin
should get the 1 for each record. If sizes are other than
integer values (e.g. 99.5), you'll have to adjust the
tests to ">=100" and ">=200", etc. for bins above 100.
5. Add whatever other data fields you want from the
table, especially the actual size value. You'll want to
check bin values against actual size later on.
6. All the above applies to Query 1. You would build
and run this query to make sure that the 1's are falling
into the correct bins when you look at the datasheet view
of the query.
7. When all is well with Query 1, you would build query
2 using query 1 as the source. The bin names created in
Q1 will be available to you in Q2. Make Q2 a totals
query, using the Sigma button on the toolbar when in
design mode. Add the bin names to the QBE grid and use
the "Sum" function in the Total line of the QBE grid
(rather than the default "Group By").
8. Run Q2 and do a sanity check on the totals.
I'll be glad to keep helping until you get over this
hump. No need to apologize for lack of knowledge - we all
try to help each other out, and it's helpful to me
learning how to pass info to others. If it comes to it,
we can talk offline via e-mail and pass some data back and
forth.
More than this, you'll need to gain experience with using
Access. In time you'll discover how powerful it is and
you'll be happy you kept at it. Good luck, and let me
know how it goes.
-----Original Message-----
Thanks for the quick response! My knowledge of Access is
fairly limited but it appears to me that the code you
provided are expressions titled "Bin**" with sizes limited
to <100, >99 and <200, etc. I don't have a clue what the
function is of "1,0" in those expressions. When I copied
your first expression into the SQL view and tried to run
the query, I received an error message stating something
about the use of an operand without an operator. Do I
need to have something before using the "IIF"? I
apologize for my ignorance. I have been trying to use the
help on the Access program but mine seems to be corrupt.
-----Original Message-----
Hi jw,
Short of creating size bin fields in your data, I think
you'll need a two-stage query process to create the bins
on the fly and then sum the results.
Query 1 - Create a calculated expression for each size
bin (e.g. BinLT100: iif(size<100,1,0); Bin100-199: iif
(size>99 and size<200,1,0), etc.
Query 2 - Use query 1 as source and sum the bins.
Hope this helps.
-----Original Message-----
I am trying to create a query that will count individuals
of various sizes. I have a table with one column of
unique id numbers and another column of sizes. In my
query I have created the expression "size < 100: Count
([fishnet no2]![fishNO])" in a blank column to count the
individuals. What code can I add to limit the count to
individuals under 100mm? I realize that I could use the
criteria option but I want the final output of this query
to show "size < 100", "size 100-199", "size 200-299",
etc.
in separate columns. When I used the criteria option for
the first count I obviously then lost all information on
individuals larger than 100mm.
.
.
.
.
