No Unique index

[Bruce Hartsell]

In Access 2003, I am trying to create a database with multiple linked tables.
I repeatedly get an error message such as "No unique index found for the
referenced field of the primary table." The message appears when I try to
"Enforce Referential Integrity" and "Cascade Update Related Fields."

I decided to try to solve the problem by creating a tiny database with two
tables as follows:


Table 1 (Name):
ID
FName
MName
LName
Status


Table 2 (Race):
ID
FName
MName
LName
Race

ID is set as the primary key in each table, with the data type as
Autonumber, the field size as Long Integer, and Index set as Yes, no
duplicates. FName, MName, and LName are each set with the same field size
(50) and the Index property as Yes, duplicates OK. (Actually, setting
indexing on the "name" fields does not seem to make a diference, since I've
tried it with and without indexing them.)

There are currently no data in the tables, although the same problem
occurred with data in the tables.

Any ideas?

--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[CamillaB]

Are you trying to set up a relationship bettwen Table 1 and table 2?. If so
the data type for field ID in table 2 needs to be a number. Then go to
relationships and drag the ID field in table 1 to table 2 and check the box
that say "Enforce Referential Integrity".

Apologies if this was not the reply you were looking for.

 

[Bruce Hartsell]

I changed the data type for the ID field in table 2 to "number," and I still
get the same error message when I try to make the relationship between the
tables.

By the way, I've seen messages suggesting that the direction in which one
makes the connection can be an issue, but it doesn't work regardless of the
direction I click and drag.
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[Jeff Boyce]

Bruce

Access Autonumbers are intended to serve as independent unique row
identifiers. Therefore, there is NO logical connection between autonumbers
in Table1 and Table2.

If you want Table2 to be related to Table1, there needs to be a field in
Table2 that holds a valid value from Table1 -- say, the RowID! But that
cannot be an Autonumber field in Table2.

By the way, if you wish to take full advantage of the power and features of
Access, you'll need to redesign your table/data structure. Including Name
info in both tables is unnecessary and redundant. Also, unless your
situation calls for multiple "Race" records per each "Name" record, you may
not need to use two tables.

I understand this was only an example, but in your example, you could get by
with a single table and include both your Status and Race fields.

A second "By the way" -- the word "Name" is a reserved word in Access. If
you use it as a table name or a field name, you will only confuse both
yourself and Access.

Good luck

Jeff Boyce
<Access MVP>

 

[Bruce Hartsell]

Jeff,

Thanks for the pointer about the design issue. Actually, I should end up
making forms to organize the data instead of making tables with redundant
data. That challenge notwithstanding, let's see if I can learn to set up
relationships properly. If you can tolerate using my example database, I
changed the ID fields so one is NameID and one is RaceID, and then I put a
field in the Race table labeled NameID with the data type set to number. I
still get the same error message. And I get the same error message if I do
the reveres, i.e. put a field labeled "RaceID" in the Name table.

I obviously still don't understand something.
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[KARL DEWEY]

Your tables seem odd to me. I would expect to see name information in only
one table with an identifier as primary key. Any other tables relating to
the individual would use the key for relations and not need to repeat names.

 

[Bruce Hartsell]

Karl,

You're right, of course, but indulge me. Remember that this test database
was to solve a bigger challenge. For discussion, let's say that I want many
data sets regarding students in a program. Instead of placing all of the
data into one huge table, I'd like data in separate tables. This would, in
some instances, be a one-to-one relationship among tables.

When I look at a table, I would like to see the full name of the student
rather than just the student ID since IDs tend to be more arbitrary than
names. Can I not link tables so that, for example, by entering the name in
one table, the name automatically appears in another table?

--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[Jeff Boyce]

Bruce

Pardon my intrusion into this thread, but this is part of my point in the
thread/reponse I offered. You do NOT want to have the same information
stored in two different fields, let alone two different tables.

Instead, store it once (in your example, in the tblName), and use a query
linked to the NameID to "look up" the name information. This is what you
could/would use for feeding a form.

In response to your response to my response (*you know, that thread-let),
let me be sure I am envisioning what you've done:

tblName
NameID (autonumber)
FName
...

tblRace
RaceID (autonumber)
NameID (long - a 'foreign key' back to tblName)
...

Does this match your design?

If so, how are you then attempting to set a relationship between these?
Please describe your process...

Jeff Boyce
<Access MVP>

 

[Maury Markowitz]

I decided to try to solve the problem by creating a tiny database with two
tables as follows:

Table 1 (Name):
ID
FName
MName
LName
Status

Table 2 (Race):
ID
FName
MName
LName
Race

The problem is that there is no relationship in these tables. Both are
completely independant. I assume you are thinking that since both have an ID
they are somehow able to be related, but in this case you have set them up as
the primary keys of their own tables (via the autonumber). Access should NOT
allow you to relate them, it is doing things correctly.

What you want is this...

Table 1 (Name):
ID
FName
MName
LName
Status

Table 2 (Race):
ID
FName
MName
LName
Race

NameID

Note the addition in the second table. Now you can add a relationship from
one to the other, joined on NameID, and it should work perfectly. Remember,
relationships are held by placing the primary key of one table into some
field in another. You did not do this in your example, so there was simply no
relationship.

A better layout would be

Table 1 (Name):
ID
FName
MName
LName
Status

Table 2 (Race):
Race

NameID

I have made the assumption that a row will exist in 2 if and only if there
is a corresponding row in 1. In this case there is no need for 2 to even have
a primary key of its own. I often add them anyway though, Access often gets
confused without them.

I hope I have explained the issue here enough, it might not be clear if you
are new to databases. But the confusion is simply that you do not have any
way to relate the fields.

Maury

 

[Bruce Hartsell]

Maury,

We're making progress, and thanks for the input. I can now relate my two
tables without getting an error message. But it doesn't work the way I
thought it would work. I thought once the relationship was established that
the data entered in one table would automatically appear in the second table.
Is there a way to do that?
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[Bruce Hartsell]

Jeff,

Thanks for jumping in. By the way, if you get two responses, it’s because
it looks like one got lost.

Although maybe I should WANT to have the same information in two tables,
even Microsoft acknowledges in my old step-by-step book that I might want to
have a bonbon name appear in my list of bonbons and in my list of recipes.
This would be a one-to-one relationships, which is what I thought I wanted.

Also, I’ve been taught that there is value in breaking tables into
manageable chunks instead of making humongous ones, and that’s what I was
trying to do, but I would like to see the names of the students in both
tables without having to enter them twice.

With input from you and others, here’s where I am:

tblName
NameID (autonumber)
FName
MName
LName
Status

tblRace
RaceID (autonumber)
FName
MName
LName
Race
NameID (long - a 'foreign key' back to tblName)

I can now establish a one-to-many link from tblName to tblRace, but the
tables don’t work as I expected. I expected all of the names to
automatically appear in tblRace as they are entered in tblName.

I worked through a Microsoft tutorial and noticed that their link uses a
combo box. If I wanted to do that, I wouldn’t do what I’ve been trying but
would just make a list for the combo box.

--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[KARL DEWEY]

In your response above to me it sounds like you want Excel worksheet.

A relational database is like an outline with lower level information
indented orin the database is in related tables. The lower level tables do
not have to contain all the higher information - only enough to link to the
higher.

 

[Bruce Hartsell]

Karl,

I definitely don't want a spreadsheet. Once I get this thing designed
properly, different staff members will uses different forms. One might use
forms called "Status Check" and "Current Placment" while another uses forms
called "Student Profile" and "Recent Graduates." Because those forms will
contain both common and unique fields, I don't want to set up a spreadsheet.

I was taught that it's best not to design huge tables but to design tables
with fields that are related to each other. But I want to see the same
student names in each of two tables. IS there not a way to set up the tables
so that once the names are enetered in one they appear in the other, or is it
possible only to do that with queries and forms?

--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[Jeff Boyce]

Bruce

It sounds like you're going to do what you're going to do, regardless of
responses from numerous folks advising otherwise. Just be aware that you'll
need to do extra work to both copy the same information over into your
second table, AND to keep the two tables in sync.

On the matter of huge tables, I suspect what you've seen in these
newsgroups, and in the tablesdbdesign newsgroup in particular, is the fact
that "wide" tables are rarely well-normalized. Again, to take full
advantage of the tools that Access offers, you need well-normalized table
structure.

But I'm not sure I ever remember seeing that "huge" tables were inherently
wrong. More like, they are a flag to check on normalization.

And are you quite certain you want one person to have (potentially) many
races? If not, you need a one-to-one relationship.

Good luck on your project.

Jeff Boyce
<Access MVP>

 

[John Vinson]

IS there not a way to set up the tables
so that once the names are enetered in one they appear in the other, or is it
possible only to do that with queries and forms?

That's not how relational databases work.

They're based on the "Grandmother's Pantry Principle" - "a place - ONE
place! - for everything, everything in its place".

Each table should contain information about one type of "entity" -
students in one table, classes in another table, and so on.
Information about that entity - student names for example - should be
stored ONCE and once only, in ths Student table. If you need to see
that information in conjunction with other data, you create a Query
joining to the StudentID.

A Race table would typically be a very small, simple lookup table with
one field and as many rows as there are races you need to track
(since, biologically, the concept of 'race' is ambiguous at best and
nonexistant at the core, this is probably a social or bureaucratic
entity, not a real one). You have a one (race) to many (members of
that race) relationship (again, typically in a bureaucratic
categorization); you'ld simply have Race as a field in the Students
table, related to the (one field) table of Races.

If you're trying to store the same information in multiple tables -
DON'T. You want to *avoid* redundancy, not create it!


John W. Vinson[MVP]

 

[Bruce Hartsell]

John,

You and others are right. Thanks for the input. I twisted something I'd
learned and got myself into a corner.

Thanks to all for the help.

--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA