One To One Relationships 1

[petervine]

Hi

I am relatively new to access (and this forum!) and wonder if anyone could please help with the following: In this scenario there are 4 tables, TblOrders and TblCourierA, TblCourierB and TblCourierC. The Courier tables are amongst other things to be used for basing a report on to print the respective courier companies consignment notes. Each courier company requires completely different fields hence the separate tables. As an order can only be dispatched with one courier each of the 3 courier tables is linked to TblOrders by a one to one relationship. The problem is how can I ensure that a user entering records can only enter data into one of the courier tables for each record in TblOrders.?

Any help would be very gratefully received. Thanks.

 

[Crowley16]

that's not a 1-1 relationship, it's a 1-many relationship from tblCourier(a-c) to tblOrders.

what you can do is to have 1 field in tblOrders, to specify which courier is being used, and if there's only 1 field, you can only specify 1 courier.

if the courierIDs have a different format, then you can just use the courierIDs as a FKey in tOrders

--------------------
Procrastinate Now!

 

[johnherman]

Crowley16 is right. This is not a 1:1 relationship. Instead, courier has 3 mutually exclusive subtypes (the 3 courier companies). A flag or code indicating which courier (and courier table) is the right approach - star for Crowley16

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[lespaul]

you may want to review The Fundamentals of Relational Database Design

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[petervine]

Thanks very much Croley16, Johnherman and Lespaul for your replies.

I except I’m being a bit slow here but there are 2 areas that I still can’t quite see. Could you explain a little further please?

My original idea was to put the common fields of the 3 courier companies into TblOrder and the remaining unique fields into their respective tables. Having a list box in TblOrder with the 3 courier companies listed would take care of only being able to selecting one courier, no problem there. The 2 difficulties I am having are;

1) If I put these unique fields into one table TblCourierABC then there will always be approx. 2/3rds of the fields left empty for each record and that further these 2/3rds should be disabled to prevent data being entered dependent on the courier selected in the list box in TblOrder.

2) If the courier table(s) represented the courier companies I could see the one to many relationship as an order can only be dispatched with one courier but a courier can deliver many orders. However the tables actually represent the consignment notes. A consignment note can only have one order and an order can only have one consignment note. Surly this must be a one to one relationship?

I hope I have explained this OK, and sorry if I am being slow here. Perhaps I should have named the tables TblConsignmentNoteTypeA ……etc. Your help is really appreciated. Thanks.

 

[lespaul]

So you have:

Couriers
Orders
and
OrdersDispatched

Orders
OrderID (PK)
Other information about the orders

Couriers
CourierID (PK)
Other information about Couriers

OrdersDispatched
OrderID (FK to Orders) }
CourierID (FK to Courier) } Composite PK

Did you review the Fundamentals document? It really is essential for designing a database.

Leslie

 

[petervine]

Thanks for the reply Leslie. Yes I did review the Fundamentals document. Infact I have printed it out and read it several times and would recommend it to anyone else starting out in data base design.

I understand the many to many relationship you have outlined, but I still cannot get my head around how this would apply. Each order (or record in TblOrders) can only have one consignment note (or therefore record in (lets say) TblCourierConsignementNote). If there was only one courier, or each of the 3 couriers in question did not each have separate unique fields, then there would be no problem as all the fields in TblCourierConsignementNote could be placed in TblOrder.

What I would like to be able to do is to relate 3 tables (each containing unique fields) to TblOrder but only allow a user to enter data into one of the three tables at any one time.

I’m not really sure how else to explain this. Thanks very much for the replies & help.

Pete

 

[lespaul]

can you post the table structures you are currently using? The tables and the fields in them.

Thanks,
les

 

[johnherman]

You really should have 5 tables:
Order
Courier (contains fields common to all couriers) - this is called a supertype.
CourierA, CourierB, CourierC = these are subtypes of the Courier supertype.

Now, can TblCourierConsignementNote be consolidated into the Courier supertype?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[petervine]

Thanks Leslie for running with this.

OK. Here’s the table structure as it stands at the moment. There are 3 couriers, ANC, Parcel Line & FedEx. Their details such as address, phone number, etc are recorded in TblSuppliers which is not involved in this scenario.

TblOrders
OrdersID (PK) (Auto Number)
Date
Etc ……. (all fields unique to TblOrders)
Weight
Height
Length
Width
etc……..(all fields that are common to the 3 consignment note tables)

TblANCConNote
OrdersID (One to One relationship to TblOrders) (Long Integer)
InsuranceUpgrade
etc…………………..(all fields unique to TblANCConNote)

TblParcleLineConNote
OrdersID (One to One relationship to TblOrders) (Long Integer)
3DayDelivery
etc………………..…(all fields unique to TblParcleLineConNote)

TblFedExConNote
OrdersID (One to One relationship to TblOrders) (Long Integer)
CustomerRef
etc………….….……(all fields unique to TblFedExConNote)

Hope that makes sence.

johnherman
Thanks for the reply. What you are suggesting is starting to make some sense. But what exactly is a supertype and a subtype and are these 5 tables all related by a One to One? How can you prevent a user entering data into more than one of the 3 subtypes at any one time?

Thanks

Pete

 

[johnherman]

You control the subtype tables just as you would in your design, programatically. The design you have is not really different than what I suggested except that what I called the Courier table you have called TblSuppliers.

Supertype and Subtype can also be thought of in the Object world. Subtypes *inherit* all attributes (characteristics) of supertypes and have additional attributes of their own.

Example 1:
Supertype: Company Executive
Subtypes: Chairman of the Board, CEO, COO, CIO, CFO, Chief Auditor, President, etc.

Example 2:
Supertype - Energy Product
Subtypes - gasoline, diesel, propane, coal, wood, kerosene, etc.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[petervine]

Thanks for the explanation of the terminology Supertype / Subtype.

So it seems that my basic table structure is OK. The one to one relationships are correct and all is normalised.

This then leads me back to the original question which now I know of the existence of subtype / supertype tables I can rephrase.

“Can I have an exclusive subtype table in Access?” To put this another way - is there any way of preventing subtype tables from having the same supertype ID number? Further can you enforce a referential integrity whereby a supertype must have a subtype and a subtype must have a super type.

I am fast coming to the conclusion that this is not possible, at least not at the table level. If this is so can anyone confirm this, in which case I’ll repost the question in the Forms forum.

Thanks everyone for all your help. Greatly appreciated.

 

[jflachman]

You do actually have a 1 to 1 relationship amongst your tables. You have One order in your TblOrders table and One Note in one of your notes fields that are connected.

The way you have this database structured is fine. However, to make it work smoothly will require some automation that may be beyond your current skill level.

The easiest way to implement it is also the least efficient. That is to make a single table TblCourierABC as you described above. You also need a CourierCompany field in your TblOrders. When user selects the courier company (from a combo box) you set ENABLED to FALSE for the controls you don't want them to modify.

You do that by putting in some code in the AfterUpdate event for the CourierCompany combo box.
[tt]
SELECT CASE Me!CourierCompany
CASE 'CourierA'
Me!CourierAcontrol1.Enabled = TRUE
Me!CourierAcontrol2.Enabled = TRUE
Me!CourierBcontrol1.Enabled = FALSE
Me!CourierCcontrol1.Enabled = FALSE
CASE 'CourierB'
Me!CourierAcontrol1.Enabled = FALSE
Me!CourierAcontrol2.Enabled = FALSE
Me!CourierBcontrol1.Enabled = TRUE
Me!CourierCcontrol1.Enabled = FALSE
CASE 'CourierC'
Me!CourierAcontrol1.Enabled = FALSE
Me!CourierAcontrol2.Enabled = FALSE
Me!CourierBcontrol1.Enabled = FALSE
Me!CourierCcontrol1.Enabled = TRUE
CASE ELSE
Exit Sub
END CASE
[/tt]

A more efficient way to handle it would be to structure your tables the way you have done, but add the CourierCompany field to your TblOrders.

On your main form, put all the controls to fill in your TblOrders then create a subform control on the main form. Call the subform control (not the subform source form) sfrmCourier.

Now create THREE new forms that will fit in that subform control. One for each of the three courier companies. Call the subforms sfrmCompanyA etc. Each form has as its source the table for ONE courier company.

Now in the AfterUpdate event for your CourierCompany field you have the following code:
[tt]
SELECT CASE Me!CourierCompany
CASE 'CourierA'
me!sfrmCourier.SourceObject = sfrmCompanyA
me!sfrmCourier.LinkChildFields = OrdersID
me!sfrmCourier.LinkMasterFields = OrdersID
CASE 'CourierB'
me!sfrmCourier.SourceObject = sfrmCompanyB
me!sfrmCourier.LinkChildFields = OrdersID
me!sfrmCourier.LinkMasterFields = OrdersID
CASE 'CourierC'
me!sfrmCourier.SourceObject = sfrmCompanyB
me!sfrmCourier.LinkChildFields = OrdersID
me!sfrmCourier.LinkMasterFields = OrdersID
CASE ELSE
Exit Sub
END CASE
[/tt]

I hope this helps


Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[petervine]

Thanks Jonathan for the reply and your solutions.

That is exactly what I am looking for!

I shall use the code from your second example. Now at least I can stop taking the headache tablets!

Pete