Hello All:

I have spent too many hours trying to resolve this prob.

I have a table that has 15 Y/N fields. I want to form-drive a query that
will return
any combination of the 15 fields selected from a form.

That is, on one instance I want to pull all rows where the 3rd, 4th, and 6th
Y/N fields
are 'Y', the rest 'N'. On another instance, I may want the 1rst, 10th, and
14th Y/N fields
'Y' to be pulled from the table.

Neither SQL's 'AND' or 'OR' satisfy the needs here. 'AND' between all 15
fields makes
the query return no rows, 'OR' between all 15 fields returns all or too many
rows. I know
there is no such thing as an 'XOR' in SQL.

Any ideas? Much appreciated!
Thanks much...
rjm (AT) soltec (DOT) net








-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

You may need to take a look at your database design. What do these 15 Y/N
fields represent? Is there a possibility that there will be a need for
additional fields in the future? I have found that often a better
alternative to Y/N fields is to have a related table (in a one-to-many
relationship).

As an (over-simplified) example, assume you have a database of contacts for
a local Habitat for Humanity and you want to know who is a contributor, who
is a volunteer and who is on a board or committee. Rather than have Y/N
fields for each of these categories, you could have a Position table linked
to the Person table by a PersonID. Your queries would then be based on a
join between the 2 tables. You would find people who are both contributors
and volunteers by using an IN clause, e.g. Position IN
("Contributor","Volunteer"). The "gotcha" to watch for is having multiple
records returned for the same person; you would need to group by Person in
your queries.

Hope this helps.

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184


"RJM" <rjm (AT) soltec (DOT) net> wrote