How do i use a lookup table for more than one field in query

  • Thread starter Thread starter Erin Freeman
  • Start date Start date
E

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.

I will also have fields that are specific to the work order regarding the
specific accommodations such as what we are going to charge for that hotel
room on that work order

I am just not sure how to have 3 accommodations based on one lookup table so
i dont have to have 3 identical tables....

I would like to choose accommodation 1 from a drop down and it autofill the
rest of the fields based on that. and so on for accommodations 2 and
accommodations 3.

Aside from having:
Acc1ID
Acc1Name
Acc1Address
Acc2ID
Acc2Name
Acc2Address
Acc3ID
Acc3Name
Acc3Address

I am not sure how to do it since the Accommodations table has about 20 fields

My ID's are as follows

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

We're not there, so we don't know the situation you are trying to model in
your data.

However, a one-to-one relationship implies that a work order can have only
one staff person, AND a staff person can only work on a single work order.
That seems an unusual working setup ... I know that I have multiple projects
I work on, and I suspect others do too.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff,

Work order to me is a contract. We send a travel nurse on a contract (work
order) to a hospital. She is the only one that will work on that contract as
it belongs to her.
 
Understood.

Does that nurse EVER work on a different contract, after the first one is
over? Are we talking about a life-time appointment, or folks dying on the
job?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi jeff,

Yes, basically how it works is these are travel nurses. each travel
assignment (contract and or work order) is a different contract.

Usually these assignments last from 4 weeks to 3 months.

I tried using the combobox wizard but had problems, is there anyway i could
email you a screen shot of my form , query and table?
 
Erin

I think we can clear this issue up easily by changing the relationship to
one-to-many for those nurses.

Even though your contracts would only (?ever) have a single nurse
associated, the relationship between the contracts table and the nurses
table is, I believe, one-to-many.

On a form covering the contract info, you could use a combobox to select a
nurse (base the combobox on the nurses table).

Or am I still missing something...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
:)
That is how i have it set up. When i create a new work order, i select the
hospital from a drop down, then i select a nurse from a drop down.

The problem i have is that i want to be able to have 3 accommodations drop
downs so i can choose up to 3 accommodations where the nurse will be staying
on that work order. I have an accommodations table that has all the
information pertaining to each accommodation.

So do i put
Accom1Name (dropdown)
Accom2Name (dropdown)
Accom3Name (dropdown)

in the work order table and then use the Accommodations table as a lookup
for those values? The problem with that is on other forms (for billing for
example) I want the actual name of the accommodations to show up not just the
ID number used for the lookup value.....However, for each accommodation
chosen i also need to have the address, phone, and other information show up
so do i need to have

Accom1Name
Accom1Address
Accom1Phone

Accom2Name
Accom2Address
Accom2Phone

Accom3Name
Accom3Address
Accom3Phone

all in the work order table? and if so how do i get the address and phone to
autopopulate with info from the accom table.....I have totally lost you
haven;t I?

This seems more difficult than it has to be...... Too bad you could not call
me or i could email you a print screen. it is hard to get the idea through
email.

Thanks :)
 
Erin

Since most of the folks here are volunteering their time, you might (...
MIGHT...) find someone with the time to take on a copy of your work and
examine it closely.

More likely you will find that asking specific questions gets you specific
suggestions.

In this case, would you plan to include the nurse's name, address, phone
number, etc. in the contract table just because you picked a particular
nurse? That would neither be necessary nor desirable if you want to get the
best use of Access' relationally-oriented features and functions. It might
be how you'd do it for a spreadsheet, but not in Access.

The question on accommodations sounds like you are trying to replicate a
spreadsheet.

If you have (and I suspect you do) a many-to-many relationship between "work
order" and "accommodations", then use a third table "work order
accommodation" to hold the valid pairs of "work order" and "accommodation".

To use a form to help, put the work order in the main form, and build a
subform that points to that third table. By using a combobox in that
subform that points to your accommodations table, you can select the
accommodation that goes with the work order.

?Got more than one? Add as many records as you need. ?Got none? You don't
need any "empty" placeholder fields!

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Erin

No, I'm not telling you to look elsewhere.

I'm saying that more specific descriptions will lead to more specific
suggestions.

Was my assessment of your "work order" X "accommodation" situation
accurate?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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.
Click to expand...

You apparently have a Many to Many relationship.

Each Nurse will stay in zero, one, two, three or more accommodations (over
time).

Each Accommodation will host zero, one, two, three or more nurses - again over
time.

You need *ANOTHER TABLE* to model this many to many relationship. Your
Accommodations table would have an AccID and information about the
accommodation itself (the hotel name, the room rate, the address, etc. etc.);
the Nurses table would have the NurseID (or you might use your ContractID, I'm
not sure). This third table would have fields for the NurseID (who is staying
in this accommodation), AccID (where is he or she stying), StartDate (when did
they check in) and EndDate (when did they check out).

This will let you enter any desired number of stays in a given accommodation.
"Fields are expensive, records are cheap" - you do NOT want to proliferate
fields in your table for this purpose!


John W. Vinson [MVP]
 
Back
Top