Access query for same parameter in multiple columns

[Rich Palarea]

Hello all:

I have an Access 2003 database and a set of ASP pages.

The database contains very basic information consisting of first and last
names and 1-10 dates.

I would like to create a query in ASP or inside of Access that I can use the
SQL from to call data from the webpage that allows the user to search one
date and find all instances of that date in all records.

Example:

Field names in Access: LunchDutyDate1, LunchDutyDate2, LunchDutyDate3...etc.

User searches for 10/1/2004

This value could appear in multiple records, but in different fields. User
ID 1 could have this value in the LunchDutyDate1 field, while UserID 2 could
have the same value in LunchDutyDate3.

I would like to return both User's records.

Thanks,
Rich

 

[Roger Carlson]

The real problem is with your table design. You should never have repeated
fields like LunchDutyDate1, LunchDutyDate2, LunchDutyDate3...etc. If each
person can have multiple assigned LunchDuties, there should be a separate
table called LunchDuty, with a field to store the Primary Key of the person
and one to store the date. With this setup, you can easily query all of the
lunchduty dates for any and all persons.

 

[Brendan Reynolds]

I agree completely with Roger's statement about the database design. If
you're dealing with someone else's design, and unable to change it, however,
you will need a query something like the following. This tests just three
columns, but hopefully it should be obvious how and where you would add the
criteria for subsequent columns. As you can see, this gets real ugly real
quick. It is also a pain to maintain if someone decides down the rode that
there can be eleven dates rather than ten. These are just two of the reasons
why it would be much better to redesign the database along the lines
suggested by Roger if at all possible.

SELECT tblTest.*
FROM tblTest
WHERE (((tblTest.LunchDutyDate1)=Date())) OR
(((tblTest.LunchDutyDate2)=Date())) OR (((tblTest.LunchDutyDate3)=Date()));

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.