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

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

"Erin Freeman" wrote in message
...
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

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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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

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

"Erin Freeman" wrote in message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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

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?

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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

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

"Erin Freeman" wrote in message
...
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?

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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

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




"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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?

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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

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 Freeman" wrote in message
...

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




"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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?

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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

thanks jeff, i will seek help elsewhere. Thanks for your time

"Jeff Boyce" wrote:

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 Freeman" wrote in message
...

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




"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in message
...
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?

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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

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

"Erin Freeman" wrote in message
...
thanks jeff, i will seek help elsewhere. Thanks for your time

"Jeff Boyce" wrote:

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 Freeman" wrote in message
...

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




"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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?

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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.

"Jeff Boyce" wrote:

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

"Erin Freeman" wrote in
message
...
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