[AccessD] Counting "unknown" fields

John,

I presume your drop-down box has two fields, one with names such as
"Family Assistance" and the other with some code value to store in place
of the full name.  This code value, say 10 for "Family Assistance," is
what you store in your records and what you want to count for your
reports.  Say you store this value in a field called PgmCode.  Then you
can write an aggregate query where you Group By PgmCode and count the
number in each resulting group.

This should get you started.  Come back to the list if you have any
problems fleshing this out.

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: John Clark [mailto:John.Clark at niagaracounty.com] 
Sent: Thursday 2003 Mar 06 10:02
To: accessd at databaseadvisors.com
Subject: [AccessD] Counting "unknown" fields

I haven't bothered y'all for a couple of weeks now, but I have painted
myself into a corner, on the program that I am currently working on. I
missed something in my early assessment of the old program that I am
replacing, and I did not see something that was there. I have gone a
different way with something, and now I am having difficulty
accomplishing the same results.

The old form looked like it had quite a bit of information on it, but
when I actually looked close, there was quite a bit of waste. For
instance, the change that I made was to convert 12 checkboxes into a
single drop-down box. I'm really not sorry for this decision, because it
served a couple of purposes, beyond simple aesthetics...the old
"hardcoded" labels for the checkboxes are actually names that are no
longer in use...it is a Social Services program, and they like to change
their program names (i.e. Since I have been here, Public Assistance
became Temporary Assistance, which is now known as Family
Assistance...All it is, is Welfare with more PC titles...they should
just call it BS, and everybody knows what that means). With the combo
box, they can change the names, whenever they desire, and they can add
and delete programs too...a couple of the current ones aren't used
anymore.

The problem is that, on one of their reports, they total out the check
boxes for each section (i.e. 95 Denied HR cases in the Lockport
office...the report is sectioned by Offices). Now that I have gone with
the drop-down method, I cannot simply add up the check boxes. And, I
cannot say count each x, because it can now change, and I don't know
what "x" is...may not do so very much, but it can.

Somehow I need to count each existing "x". I need to know that there are
5 x's, 6 y's, and 8 z's, but the next year I might also have a "q" that
was added and I'll need to count that.

Any ideas?