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
|
|||
|
|||
Desperate! Look up values
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 |
#2
|
|||
|
|||
Desperate! Look up values
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" wrote: 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 |
#3
|
|||
|
|||
Desperate! Look up values
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 "mscertified" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Desperate! Look up values
Yes, i have it like you say, one nurse has one work order, but during that
work order can stay at up to 3 different accommodations. The only way i can get it to work is to have 3 exact same accommodation tables which i know is wrong. I just want to be able to use the accommodation table as a look up so in my query it would look like this Nurse WO# Hotel Phone BillingAmt (This is Work O spec.) jane workorder 1 Ramada Hotel 555-44444 60 jane workorder 1 Sandman Hotel 111-5555 55 jane workorder 1 Holiday Inn 444-0000 40 I need some advice Do i do the lookup information in the query, or in the drop down box that will contain the accommodation names??? please help "mscertified" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
Desperate! Look up values
On Dec 12, 9:53 pm, Erin Freeman
wrote: "mscertified" wrote: 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. Yes, i have it like you say, one nurse has one work order, but during that work order can stay at up to 3 different accommodations. The only way i can get it to work is to have 3 exact same accommodation tables which i know is wrong. I just want to be able to use the accommodation table as a look up so in my query it would look like this Nurse WO# Hotel Phone BillingAmt (This is Work O spec.) jane workorder 1 Ramada Hotel 555-44444 60 jane workorder 1 Sandman Hotel 111-5555 55 jane workorder 1 Holiday Inn 444-0000 40 I need some advice I think mscertified's schema needs a little tweaking. I don't see how WorkOrderID can be an attribute of an accommodation entity, therefore has no place in the Accommodation table. I think an additional table is required to model the relationship between work orders' staffing and accommodation entities. Similarly, a nurse is an entity in its own right and there is a design rule of thumb which states that a table models either an entity or a relationship but not both (otherwise you get 'update anomalies' where you get into situations where you cannot demonstrate you need to hire a nurses to fulfil work orders because you cannot create a work orders without first having nurses etc), therefore another table is required to model the relationship (1:1 this time) between work orders and nurses. Also, the tables are missing even the most basic constraints: Nurse table --NurseID -- unique constraint --NurseName WorkOrder table --WorkOrderID -- unique constraint WorkOrderNurse table --WorkOrderID -- unique constraint, foreign key constraint referencing Work Order table --NurseID -- foreign key constraint referencing Nurse table --unique constraint on the compound of (NurseID, WorkOrderID) Accommodation table --AccomID -- unique constraint --Phone etc. WorkOrderAccommodation table --WorkOrderID --NurseID -- foreign key constraint on the compound of (NurseID, WorkOrderID) referencing WorkOrderNurse -- AccomID -- foreign key constraint referencing Accommodation table -- unique constraint on the compound of (AccomID, NurseID, WorkOrderID) -- table constraint to ensure that WorkOrderID appears no more than three times -- BillingAmt -- etc For the table constraint to ensure the relationship is 1:1..3, I can suggest two approaches: 1) Use a sequence column of type integer with a validation rule to restrict its values to one of three distinct values (e.g. seq_col BETWEEN 1 AND 3) then create a unique constraint on the compound of (WorkOrderID, seq_col). 2) Use a table-level CHECK constraint e.g. CHECK (NOT EXISTS ( SELECT T1.WorkOrderID FROM WorkOrderAccommodation AS T1 GROUP BY T1.WorkOrderID HAVING COUNT(*) 3)) [Aside: I can imagine an auditor examining the above schema and wondering why there is no temporal element to nurses' accommodation for work orders e.g. what is there to stop a single night's accommodation being attributed to more than one work order...? I assume the auditor would demand additional information to ensure fraudulent claims for accommodation aren't being made.] With the schema tweaked, the required query will look something like: SELECT W1.NurseID, N1.NurseName, A1.Name AS Hotel, A1.Phone, W1.BillingAmt FROM (WorkOrderAccommodation AS W1 INNER JOIN Accommodation AS A1 ON A1.AccomID = W1.AccomID) INNER JOIN Nurse AS N1 ON N1.NurseID = W1.NurseID; Jamie. -- |
#6
|
|||
|
|||
Desperate! Look up values
Thanks Jamie I think i need to explain the situation. We are a travel nurse company. We hire nurses to fill the shortages in the hospitals. We get a request from the hospital, we then look at our pool of nurses that we have and find the right one for the job. We build a work order, we assign Sally to the work order. We then need to arrange her travel. The duration of her work order is 2 weeks. She will stay at the Holiday inn for 2 nights, and then will move in to hospital housing for one week and then she will move into our corporate house for the rest of the duration So she has 2 accommodations. My tables are fine, My database is huge and this is the last obstacle. I have a table full of all of the accommodation information. The accommodation table consists of information such as name, phone number, whether it has a pool, what the telephone account number is etc. All I need is to be able to have fields in my work order table that will use the accommodation table as a lookup. Since there is up to 3 options of accommodations for one work order, i was having a hard time wrapping my head around it. Currently the way (and i know its wrong) is I have: 3 seperate but identical Accommodations tables, I have Accom1ID,Accom2ID and Accom3ID's in the Work Order Table to join the 3 seperate Accommodation tables to, using the ID's I have lookups. However this is not right. All i need is a way to use that one table for the lookup for 3 seperate Accommodation options in the "Contract" Query The Contract Query is like this - Tables involved: Work Order , Nurse, Accommodations WorkORderID NurseID ---------------joined to NurseTable as 1 to 1 (1 work order has one nurse Accom1ID ---------------joined to Accom1Table as 1 to many Accom2ID ---------------joined to Accom2Table as 1 to many Accom3ID ---------------joined to Accom3Table as 1 to many is there anyway i could send you a screen shot of my query and my tables and form. I think this is simpler than you guys think, i am a beginner..... Thanks in Advance "Jamie Collins" wrote: On Dec 12, 9:53 pm, Erin Freeman wrote: "mscertified" wrote: 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. Yes, i have it like you say, one nurse has one work order, but during that work order can stay at up to 3 different accommodations. The only way i can get it to work is to have 3 exact same accommodation tables which i know is wrong. I just want to be able to use the accommodation table as a look up so in my query it would look like this Nurse WO# Hotel Phone BillingAmt (This is Work O spec.) jane workorder 1 Ramada Hotel 555-44444 60 jane workorder 1 Sandman Hotel 111-5555 55 jane workorder 1 Holiday Inn 444-0000 40 I need some advice I think mscertified's schema needs a little tweaking. I don't see how WorkOrderID can be an attribute of an accommodation entity, therefore has no place in the Accommodation table. I think an additional table is required to model the relationship between work orders' staffing and accommodation entities. Similarly, a nurse is an entity in its own right and there is a design rule of thumb which states that a table models either an entity or a relationship but not both (otherwise you get 'update anomalies' where you get into situations where you cannot demonstrate you need to hire a nurses to fulfil work orders because you cannot create a work orders without first having nurses etc), therefore another table is required to model the relationship (1:1 this time) between work orders and nurses. Also, the tables are missing even the most basic constraints: Nurse table --NurseID -- unique constraint --NurseName WorkOrder table --WorkOrderID -- unique constraint WorkOrderNurse table --WorkOrderID -- unique constraint, foreign key constraint referencing Work Order table --NurseID -- foreign key constraint referencing Nurse table --unique constraint on the compound of (NurseID, WorkOrderID) Accommodation table --AccomID -- unique constraint --Phone etc. WorkOrderAccommodation table --WorkOrderID --NurseID -- foreign key constraint on the compound of (NurseID, WorkOrderID) referencing WorkOrderNurse -- AccomID -- foreign key constraint referencing Accommodation table -- unique constraint on the compound of (AccomID, NurseID, WorkOrderID) -- table constraint to ensure that WorkOrderID appears no more than three times -- BillingAmt -- etc For the table constraint to ensure the relationship is 1:1..3, I can suggest two approaches: 1) Use a sequence column of type integer with a validation rule to restrict its values to one of three distinct values (e.g. seq_col BETWEEN 1 AND 3) then create a unique constraint on the compound of (WorkOrderID, seq_col). 2) Use a table-level CHECK constraint e.g. CHECK (NOT EXISTS ( SELECT T1.WorkOrderID FROM WorkOrderAccommodation AS T1 GROUP BY T1.WorkOrderID HAVING COUNT(*) 3)) [Aside: I can imagine an auditor examining the above schema and wondering why there is no temporal element to nurses' accommodation for work orders e.g. what is there to stop a single night's accommodation being attributed to more than one work order...? I assume the auditor would demand additional information to ensure fraudulent claims for accommodation aren't being made.] With the schema tweaked, the required query will look something like: SELECT W1.NurseID, N1.NurseName, A1.Name AS Hotel, A1.Phone, W1.BillingAmt FROM (WorkOrderAccommodation AS W1 INNER JOIN Accommodation AS A1 ON A1.AccomID = W1.AccomID) INNER JOIN Nurse AS N1 ON N1.NurseID = W1.NurseID; Jamie. -- |
#7
|
|||
|
|||
Desperate! Look up values
On Dec 13, 4:33 pm, Erin Freeman
wrote: My tables are fine I have Accom1ID,Accom2ID and Accom3ID's in the Work Order Table Good for you. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|