Indeterminate Relationship

[LouiseJ]

I have problem which I can’t quite balance in my brain as follows;

TblDesigns
Customer Account Number}Composite (duplicates allowed) – FK
Design Number }Composite (duplicates allowed) - PK

TblCustomers
Customer Account Number - PK

TblOrder
Order ID – PK
Customer Account Number – FK

TblOrderDetail
Order Details ID – PK
Order ID - FK
Design Number – FK

I want to be able to select a customer’s design number in TblOrderDetail based on the Customer Account Number in TblOrder. I have tried relating in all directions but keep coming up with a blank or the all well too known ‘Indeterminate Relationship’

Any Ideas Please!

Thanks

LouiseJ

 

[RoyVidar]

This I don't understand - you're trying to set up a relationship between Customer Account Number in tblDesigns, and Design Number in TblOrderDetail?

Why?

You have marked the Design Number in the tblDesigns as primary key, then that field would be the field to establish relationship with Design Number in tblOrderDetail, wouldn't it?

Or are Customer Account Number AND Design Number in tblDesigns a compound/composite primary key? If so, you'll need both fields in the tblOrderDetail.

Point is - to make Access understand a 1:M relationship, you'll need a unique index (as you'll get with a primary key index) on the 1 side.

And - just a practical tips, drop the spaces in the field names (and special characters, should you be using that, too).

Roy-Vidar

 

[LouiseJ]

Thanks Roy for the Reply.

I take your point about the spaces in field names and will amend accordingly.

The situation I am modelling here is that there can be many incidences of the same design number (say 09). The design numbers all have a customer (say account number W400) which then makes then unique (Hence the compound key in TblDesign) There can be many designs numbered 09, but each customer can only have one design numbered 09. ie it is the customers ninth design. The unique compound key here would be a combination of W400 & 09.

Your suggestion of setting up a relationship between DesignNumber in TblOrderDetail and DesignNumber in TblDesign results in an Indeterminate Relationship.(This I guess is because access does not know which design number to relate to as there are many number 09’s in TblDesign). I have also tried setting a relationship between the CustomerAccountNumber in TblOrder and CustomerAccountNumber in TblDesign. Same result – Indeterminate Relationship.

What I need (or at least what I think I need!)is a way to relate both CustomerAccountNumber in TblOrder and DesignNumber in TblOrderDetail to TblDesign in such away that only the design numbers for the customer in TblOrder can be related to the DesignNumber field in TblOrderDetail.

Thanks.

LouiseJ

 

[RoyVidar]

Let's try again, as I perhaps still don't understand the question.

If it's about relationships
The whole deal of relationships, is about having fields in both tables that are like/equal, so that the database can perform a join. In a 1:M relationship, it means the 1 side will need a unique primary key (or candidate key, for that sake, but one usually use the primary key, the point is having a unique identifier). This unique primary key, whether it contains only one field, or a combination (compound/composite), will be used as foreign key in the related table. Else the relationship dos not have "any meaning", it is not possible to determine which record on the 1 side, the record on the M side relates to -> indeterminate.

- if you want to relate tblOrderDetails and tblDesigns, then you will have to put the whole primary key of tblDesigns into tblOrderDetails. Which means both the Customer Account Number and Design Number.

Reasons, as you stated, the Design Number (0-9 per customer) in tblDesigns is not unique, so by using that, access can't find any uniqueness -> "indeterminate". The Customer Account Number, in tblDesigns, isn't unique either (up to 9 designs per customer) - > "indeterminate".

So - to link those tables, set up a relationship between them - > the whole primary key of tblDesigns will need to be used as foreign key in tblOrderItem.

Now, since the Customer Account Number isn't propagated into tblOrderItem, this wouldn't create RI (Referential Integrity) to enforce selection of designs only relating to this customer. To create RI for this, you'd need more work on the table designs. The other way, is to enforce it through the interface.

Enforcing the "select only related Design per customer"
I suppose, to enforce, or make it possible to only select designs related to a specific customer in the user interface, you could probably do that through tweaking the rowsource of a combobox.

Let's say you have a frmOrder, and on it the subform frmsubOrderDetails. The rowsource of a combo on this subform, could then be something like this:

[tt]select [Customer Account Number], [Design Number]
from tblDesigns
where [Customer Account Number] = forms!frmOrder!txtCustAccNo[/tt]

Where txtCustAccNo is a control on the main form (order form) bound to the Customer Account Number.

But again - to store the chosen design in the tblOrderItem table, so that it points to one design (design 09 for W400), you will need both the Customer Account Number and Design Number in the tblOrderDetail table.

For practical purposes, compound/composite primary keys propagated as foreign keys in child tables, needs a bit more tweaking, cause the interface of Access is mostly based on Autonumbers as primary key for any table... so, to save some work, if you keep this table structure, you might save some work by using an Autonumber as primary key for your design table, but still keep a compound/composite unique index on the Customer Account Number and Design Number. You's still need some work, but then you'd only store the unique one field primary key within the Order Detail table.

Some reading that's often recommended here at Tek-Tips to understand tables and relationships Fundamentals of Relational Database Design.

Roy-Vidar

 

[LouiseJ]

Thanks Roy for your detailed reply. I have resolved the problem.

I have kept the compound key of Customer Account Number and Design Number in TblDesigns but have added an auto number field to the table and used that to link to TblOrderDetail. I shall then use a combo box in a form to look up the design number as you have suggested.

Thanks a million!

LouiseJ