Query and table problems.

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have what should be a relatively easy problem to fix but
I just can't seem to get there from here. I would
appreciate any help that could be provided.

I have a database with two tables. In one table I keep
the information about a group of people. There are
several fields that I want to perform calculations on, and
they are all check boxes. (i.e. count how many people
have each field checked) I want to store those values
(update) in a second table.

I have created a query that tells me how many people have
each box checked, but I just can't seem to save that
number in my second table. I have checked all the
knowledge base questions and can't seem to get it to
work. I either get a prompt for "Enter Parameter Value"
or errors that it is not part of the aggregate function or
numerous other errors based on the techniques that I try.
I would appreciate any assistance or suggestions that
anyone has. Thanks.
 
Paul

You did say "... any suggestions...", right?

Any time you have repeating fields ("...several fields ... and they are all
check boxes"), your database probably needs further normalization. A data
structure like what you described sounds like ... a spreadsheet!

In Access, a well-normalized design will let you get the full power and
benefit from the functions and tools Access offers. These tools are design
to use ... normalized data!

Another approach to multiple possible characteristics (you are using
checkboxes to do this now) is to use just two fields. You could use a table
something like:

trelPersonCharacteristic
PersonID
CharacteristicID

This pre-supposes that you have a list (table) of Persons, and a (lookup)
table of Characteristics, each with their own ID field. The above-described
table records valid pairs of person X characteristic. If a person had, in
your scenario, 3 checkboxes checked, this new approach would have three
rows. Access can easily count, in a query, how many characteristic rows
PersonID = 17 has, or, conversely, how many rows (persons) that
CharacteristicID = 3 has.

Careful what you ask for?!
 
Back
Top