Having (1)user input look for information in multiple fields

[Guest]

I have created a database to help with the scheduling staff members. It is
possible for staff members to be listed in up to 18 different fields. What I
want to do is create a query that would allow the user to input the staff
persons name and have the querey look through all of the fields for each day
and display the days that they are signed up for.

 

[Guest]

This sounds like a badly designed database. You should not have 18 fields in
the same table where the same staff member could be listed. There are quite a
few articles on database design on this website.

HTH
Martin

 

[Guest]

Ok I will look for those articles. Even if I revise my database to limit the
number of fields can you advise if what I was asking is possible?

Thanks

 

[Guest]

If you had 2 tables Staff (id,name and other stuff) Schedule(id, staff id,
sch date, shift and other fields as needed) then it would be rather simple
here is an example

SELECT schedule.*
FROM schedule
WHERE (((schedule.[staff id])='100') AND ((Schedule.[sch
date])=#11/08/2004#));

I didn't use plain date bec. it is a reserved word. I included shift as a
field in the schedule table this would allow you to have as many shifts per
day as needed. You could add a third table called shifts if each shift had
some information only associated with each shift.

HTH
Martin

 

[John Spencer (MVP)]

Yes, what you were asking for with you old design is possible.

You would need to put the same criteria in 18 different criteria lines. OR you
could use something like the following query. This would be easy to input but
slow doing the search.

SELECT StaffMember
FROM SomeTable
WHERE [Enter Staff Identifier] IN
(Field1, Field2, Field3,...,Field17,Field18)

In the query grid, that would look like
Field: [Enter Staff Identifier]
Criteria: In (SomeTable.Field1, SomeTable.Field2,...,SomeTable.Field18)

This will be slow since the query won't be able to use any indices. IF you
implement a revised table structure, this query would be simpler and quicker.