Using Criteria for a Y or N response

T

TKorell

I am using a large database and I want to have it give me only the Y
responses not the N responses for the labels. This might sound nuts but
there are 12 fields I want it to pick from
Like if FName has a Y in any of the fields I want that name but if they
don't I don't want the name. Could someone help me out?




SELECT Genmail.FNAME, Genmail.LNAME, Genmail.COMPANY,
Genmail.MailingAddress, Genmail.CITY, Genmail.STATE, Genmail.ZIP,
Genmail.[303(d) Update], Genmail.[Albion River], Genmail.Ammonia,
Genmail.Bacteria, Genmail.[Big River], Genmail.[Blue-Green Algae],
Genmail.[Butte Valley], Genmail.[Elk River], Genmail.[Estero Americano],
Genmail.[Freshwater Creek], Genmail.[Garcia River], Genmail.[Gualala River],
Genmail.[Jacoby Creek], Genmail.[Laguna de Santa Rosa], Genmail.[Lost River],
Genmail.[Mattole River], Genmail.[Navarro River], Genmail.[Noyo River],
Genmail.Nutrients, Genmail.[Pudding Creek], Genmail.[Redwood Creek],
Genmail.[Stemple Creek], Genmail.[Temperature/DO], Genmail.[Ten Mile River],
Genmail.[Van Duzen River]

FROM Genmail;
 
V

vanderghast

Not a good design. If you ever want to consider the Colorado River, you add
a 13rd field and change all your queries, forms, reports, ... to take into
account this new field?


Anyhow, assuming Y and N are, in fact, BOOLEAN fields (true and false), add
a WHERE clause


WHERE field1 OR field2 OR field3 OR ... OR field12


If your fields are strings instead of Boolean, then more typing involved:

WHERE field1="Y" OR field2="Y" OR ... OR field12="Y"


or, less typing:


WHERE "Y" IN( field1, field2, field3, ..., field12)


But in any cases, I would change the table design, if possible, to get
something more robust.



Vanderghast, Access MVP
 
J

Jeff Boyce

I'll second Vanderghast's observation...

Your structure looks more like a spreadsheet than a relational database
table.

Until you normalize your table, both you and Access will have to work
overtime to overcome the 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Top