If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#3
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#4
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#5
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#6
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#7
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#8
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#9
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
#10
|
|||
|
|||
How do i use a lookup table for more than one field in query
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 |
|
Thread Tools | |
Display Modes | |
|
|