Interminate Relationship

[Maurita]

Hi all, hope someone can help with a problem I am having. I have a
total of four tables. Table 1 (the main table) is called EXPEDITES
and has a field called OverhaulNumber to hold the value of Table 2,
OVERHAUL. The OVERHAUL table has four fields (OverhaulFacility,
EngineSerialNumber,OverhaulDate, and OverhaulNumber). Tables 3 and 4
are titled tblOverhaulFacility and tblEngines, which hold information
that the OVERHAUL table pulls from.

Anyway, since the OVERHAUL table must be unique, I have set the
OverhaulNumber as an autonumber field. I have set multi key fields
for OverhaulFacility, EngineSerialNumber and OverhaulDate; which work
great.

The major problem I am experiencing is that I am having trouble
creating a relationship between the OVERHAUL table and the main table
of EXPEDITES. When I try to create a relationship, it is of an
Indeterminate Type. If I take off the multiple key fields and just
usse the OverhaulNumber autonumber field as the primary key, the
relationship works fine. But, I must have unique records in the
OVERHAUL table.

I would appreciate any help with this matter.

Thank you.

Maurita Searcy

 

[NetworkTrade]

Table 1 as the main table should not have or need any values from Table 2

Table 2,3,4 should have Table 1's key field in it for the cross reference.

 

[Steve Schapel]

Maurita,

The correct way to set this up is to make the OverhaulNumber field the
Primary Key of the OVERHAUL table.

If you want the combination of OverhaulFacility, EngineSerialNumber and
OverhaulDate fields to be unique, then the way to achieve this is to
create an Index in the table design to define this.

 

[Maurita]

Maurita,

The correct way to set this up is to make the OverhaulNumber field the
Primary Key of the OVERHAUL table.

If you want the combination of OverhaulFacility, EngineSerialNumber and
OverhaulDate fields to be unique, then the way to achieve this is to
create an Index in the table design to define this.

--
Steve Schapel, Microsoft Access MVP






- Show quoted text -

Thank you so much for your e-mail, but I am so very confused. I tried
your suggestion, but I must be doing something wrong. I understand
that the OverhaulNumber field should be the primary key to the
EXPEDITE table, but if I make the other three fields
(OverhaulFacility, EngineSerialNumber, OverhaulDate) unique, then I
will not get duplicatess in these fields in the EXPEDITE table, which
I must have. In the OVERHAUL table, there will be duplicates of
OverhaulFacility, EngineSerialNumber, and OverhaulDate, with the
OverhaulNumber being the record identifier and the field that needs to
link to the EXPEDITE table. I am trying to achieve a unique record
of OverhaulDate, EngineSerialNumber, and OverhaulFacility. I thought
that if these three fields were primary keys, that there would be no
way that there would be duplicate records in my table, which there
isn't. But, I also need to link to the EXPEDITE table by the
OverhaulNumber, and therein lies the problem.

I am sorry for my confusion in this matter.

Maurita

 

[Steve Schapel]

Maurita,

Can you confirm that I have understood this correctly... The EXPEDITES
table can have more than one record for each Overhaul? In other words,
what we want is a one-to-many relationship OVERHAUL=>EXPEDITES?

And also, it seems from your description that you have the
OverhaulFacility, EngineSerialNumber, and OverhaulDate fields in *both*
tables... is that correct?