Finding Specific data

[Antonio]

I have a table that contains 17 different data fields that
contain either an "A", "P", or "NA". I need to create a
report that lists all the records that have an "A" in all,
some, or one of the data fields. I am having trouble
coming up with a query or expression that would create a
query for me to base the report on. Can anyone help?

 

[Steve Schapel]

Antonio,

The basic problem here is that the design of your table structure is
incorrect, and as such, this type of query will always be awkward. If
you are interested in exploring this aspect, please post back with
examples of the 17 fields, and someone will be able to advise on an
improved, more normalised table design.

In the meantime, one way to approach the problem is to make a
calculated field in your query like this...
HowManyA: Abs(([1stField]="A")+([2ndField]="A")+...+([17thField]="A"))
This will give the number of fields with "A", and you can then base
another query on this to divide the records into your all, some, one
categories. However, you might also need to do an initial query to
assign alias field names, otherwise the calculated field expression
might exceed the limit of 255 characters.

- Steve Schapel, Microsoft Access MVP