Desperate! Look up values

[Erin Freeman]

Hi,

Here is the situation,

I have one nurse who may stay in 3 different accommodations on one work order.

I have one tablel that contains all of the accommodations information
(phone, cleaning service, tv etc)

My query is all set up and works except on allowing me to do the following...

I just need to simplist way to use that one accommodations table as the
lookup for the 3 accommodatins the nurse will stay at. and so when i choose
Accommodations ID 2 from a drop down the rest of the fields, (Accom name,
phone, address, rate etc) auto populate.

I am guessing my structure is obviously wrong.

Currently for some reason right now when i choose one and then go to choose
the other they all change so they are the same...please help....

Thanks in advance

 

[mscertified]

You have not really given us enough information on your tables. You need a
structure that models what you are trying to accomplish. i.e. it must store
multiple accomodations per work order per nurse.

Nurse table
--NurseID
--NurseName

Work Order table (multiple records per nurse)
--WorkOrderID
--NurseID

Accommodation table (multiple records per work order)
--AccomID
--WorkOrderID
--Phone etc.

If your nurse can have one accomodation while working on 2 or more work
orders then the above may not work.

-Dorian

 

[Erin Freeman]

ok,

so my query is this

I have a work order table which has a one to many relationship with an
accommodations table and a one to one relationship with a staff table

This tell me that I have 1 work order and that work order can have many
accommodations but only one staff member.

Now what i need to be able to do is have 3 drop down combo boxes on my work
order form allowing me to choose 3 different accommodations for that one work
order for that one nurse where all of the information for the accommodations
come from the accommodations table.

Does that make a little more sense?

My ID's are as follows

Work Order Table - Work Order ID
Accommodations - Accommodations ID
Staff - Staff ID