Question about a Query

  • Thread starter Thread starter WanaLEE
  • Start date Start date
W

WanaLEE

Ok so I am new to using Access and I am basicly self taught, so I am sorry if
the terms are wrong. My question is I have a table with several fields and
under most of them I added a drop box with the same selections (Acceptable,
Unacceptable, and Not Applicable). I was to have a query where I can run it
and it add up each field and give me the value of all the acceptable for each
field and then an over all total, another one for the unaccpetable, and
another one for the Not Applicable. How would I do this..is it even possible
or will I have to do the check box route? Thanks!
 
Neither.

It sounds like you have something you are measuring, and you are making
several evaluations about it (lots of fields where you evaluate it as
acceptable, unacceptable, or not applicable.) In a relational database, this
should be modelled with a related table.

You need 3 tables like this:
Main: one record for each thing being tested, with a primary key field
MainID.
Test: one record for each kind of test you conduct.

TestResult, with fields:
- MainID says what is being tested
- TestID what test this is
- Result acceptable or unacceptable.

You interface this with a main form for entering the main thing, and a
continuous subform for the test results. The subform gets one *record* for
each applicable test. In the subform you will probably use a combo box for
selecting the tests that were conducted (one per row.)

Now it's laughably easy to create a Totals query that gives you the count of
acceptable and not acceptable results for each MainID item.

This structure is absolutely essential to come to grips with in a relational
database. For another similar example, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html
 
Back
Top