Building a Relatioship

[Daniel Lamb]

I have a database for files stored on CD and I am trying to link two tables
called Media Resources and File Info.

Media Resources has the following fields
ID
Disk
Capacity
Storage Location

File Info has these fields:
ID
Disk
Name
Bytes
Delete Date
Available off-line

I want to create a relationship using the ID field which is formatted as
Autonumber for each of the tables. Each name in the Disk field of Media
Resources is unique. Records in Disk of File Info are to have many
duplicate entries for more than one file is on a disk. By building a
relationship of the two tables, I want to be able to use the number values
in Media Resources Capacity for calculations involving file sizes under File
Info Bytes.

I've tried to establish this relationship in the builder. It's one-to-one
for there are no other tables in the database. I think the join should be
All records in File Info and only those in Media Resources where values
match. I can not set a join that will satisfy Access for enforcing
referential integrity to carry out cascade updates or deletes of related
fields. I receive the error 'Invalid field definition 'id' in definition of
index or relationship.'

Once I get a relationship with enforcing referential integrity that works, I
should be able to add or delete records based on queries, yes?

Thanks.

 

[Greg Kraushaar]

I've tried to establish this relationship in the builder. It's one-to-one
for there are no other tables in the database. I think the join should be

No its not one to One. Its One to many as Each Media record can have
MANY records in the FileInfo table

It is the records that matter for a relationship, not the number of
tables.

To successfully set up an Enforced One TO many relationship (with or
without Cascades) You cannot use cascades unless the link is enforced.

One side
must use Primary key on a single field (AutoNumber is best)

On the many side
This must link to a field in the other table that is indexed with
Allow Duplicates.
NULLs are not allowed

Field type on both sides of the link must be compatible. If you use
AutoNumber on the one Side, use LongInteger on the many side.

All link fields on the many sides must contain a value that
corresponds to a value already in the one side.

If your tables do not adhere to all these rules, Access will not allow
you to make the relationship. You will need to fix your tables/data
first.

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

 

[Daniel Lamb]

Greg,

Thank you for taking the time to reply.

I get a sense from what you write and what I have tried from your advice
that I may have a wrong premise as to what two tables linked by a
relationship can do. I'm under the belief that by linking two tables I can
reduce a lot of data that would otherwise by duplicated in fields of the
primary table. In this case, listing in each and every record the disk
characteristics and where the disk is stored for a particular file.

I followed your advice for how to set-up a numbered field using
AutoNumber on one side and long integer for the similar field on the many
table. Access gave me an error that a join with enforced referential
integrity could not be established for it appeared there were records in the
Listings table that did not appear in the Resources table. To me, this is
exactly what I want. The Resources table has 82 records for disks and where
they are stored plus other data about disk characteristics. The listings
table has 276 records for all the files that are stored on those disks.

By what you say and what Access is demanding, I will end up with two
tables of 276 records each, linked by a unique record id.

 

[Daniel Lamb]

Greg,

Since my post to you yesterday, I have done some more work to get a
hold on relationships.

I took my two tables and merged them into one. I then ran the table
analyse wizard within Access. Through it, I was able to split up the table
as I wanted (Media Resources and File list) and it devised a Look-up for the
Media Resources which transfers data into File list as new files are added
to the table.

Your help on how relationships need to be set-up was better than
anything else I have learned on the topic. Using what you provided, I
fooled around with the concepts on a couple of other databases and I now
have a firm grasp on the concepts and some re-engineering to do. I have a
number of databases that should be multi-table relationships for compactness
and other factors. Thanks for your help!

Dan