Queries - Two employees for one procedure

I have a Procedure Table in which nurses are scheduled to perform the procedure. There is usually one nurse, but sometimes two. I created a look_up field for Staff 01 and Staff 02 based on a Staff Table. How do I then write a query to get One nurses schedule of procedures if that one nurse might be in either of the fields?

Welcome to ASP Free Forums! What you described is really a many-to-many relationship (a nurse may be scheduled for "many" procedures and a procedure may have "many" nurses--even if it's never more than 2, the key point is that there may be more than one).

In relational databases, a many-to-many relationship requires 3 tables: one for the nurses, one for the procedures, and a joining table that has just the foreign keys for each of the other 2 tables and perhaps other data, such as a date.

This makes it straightforward to create queries to produce results like you're asking for.

That's of course greatly simplified. But that's the structure that supports many-to-many relationships. You can create data entry forms that let you select a nurse and a procedure from comboboxes and perhaps a date or other data in a textbox, and automatically generate the records for the 3rd table, which constitutes your scheduling data.

Your approach, to use 2 repeating fields (Staff01 and Staff02) is more intuitive, but makes it very difficult to retrieve data, just as you have discovered, which is why relational database operation is somewhat difficult for newcomers to grasp.

In this case - I have a Staff Table, a Procedure Table, and a Scheduling Table - but I have to schedule maybe two staff to one procedure in the Scheduling Table without the option of listing that procedure twice, once for each staff.....what do i do in this instance?
I have:
Staff Table --------Scheduling Table-------Procedure List
I need two staff listed in the Scheduling Table for One Procedure which is selected from a drop-down list.

don94403 is right about database design and that is the ideal but sometimes can carry data normalization just so far.

Your original setup can be made to work in several ways.

If you want the nurse names to show only once in report if in either field, use a 'constructed' field in your query:
SELECT nz(staff1, staff2) As nursename FROM Procedures

or if you want to use a nurse's name as criteria:
SELECT procedureID FROM procedures WHERE staff1=nursename or staff2=nursename

I still believe that a normalized database is far easier to construct than struggling with an unnormalized database and getting something that may work for the immediate task, but later when requirements change, is so inflexible that you can't use it anymore.

The usual way to handle it is what I described. Design a Form that has 2 combobox controls, one for selecting the Staff member, one for selecting the Procedure. Depending on what other fields you may want in the 3rd table, you might want additional controls on the Form. You will also need a command button that is used to Save a new record in the 3rd table, with a couple of lines of VBA code in the Event Procedure behind the onClick event of the button.

The way you assign 2 staff to the same procedure is simply to select the procedure in one combobox and the first staff member in the other combobox and click on Save, then select the second staff member and click Save again. If you needed to assign a 3rd staff member, just do it again (maybe you never will need to do this, but my point is that it is flexible).

Here is what my form looks like: based on Scheduling Table:
Time---Procedure (from Table)---Staff1---Staff 2----etc.

I have the three tables:
Procedures
Staff
Scheduling

I have the form with the combo box for Procedures and Staff
Staff1 is chosen from Staff Table
Staff2 is chosen from Staff Table
Procedure is chosen from Procedure Table

I need to list each nurse with a procedure whether in Staff1 or Staff2 - so that they can have their own procedure schedule for the day. So you're saying to create another table that would generate from the form saving each procedure with each nurse? I don't know how to do that. Can't a report or query be used to extract the procedure and Staff1 and then Procedure and Staff2 and then combine them together in a query?

You continue to think in terms of having exactly two nurses associated with each event, which will lead to many problems, because it is what experienced database people call "unnormalized."

There should be ONE combo box on your form for "staff." Each relationship between a nurse and an event is a separate connection, conceptually, and will result in a separate record in the 3rd table that I previously described for you. Read what I wrote again. To assign one nurse to an event, select the procedure and the nurse, and whatever other data (date, case #, whatever) and press Save. To assign two nurses, do the same thing again. To assign three nurses, do it 3 times. Do you see what I'm saying? This is the way databases work. You will then have a data structure that supports simple queries that can show the nurses assigned to any or all events, or what events are scheduled for any or all nurses, etc. etc. When you try to put 2 relationships into one record, the way you are still thinking about it, the tools of relational databases (primarily, SQL) won't work to produce the information you want.