Desperate! Look up values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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 :(
Click to expand...

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
Click to expand...
WorkOrder table
--WorkOrderID <-- unique constraint
Click to expand...
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)
Click to expand...
Accommodation table
--AccomID <-- unique constraint
--Phone etc.
Click to expand...

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.

--
 
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 said:
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 :(
Click to expand...

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
Click to expand...
WorkOrder table
--WorkOrderID <-- unique constraint
Click to expand...
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)
Click to expand...
Accommodation table
--AccomID <-- unique constraint
--Phone etc.
Click to expand...

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.
Click to expand...
 
Back
Top