Primary Keys

[potter]

I have assigned 2 fields as the primary key. I can link one of the fields to
another table but not the other. I keep getting the following error
message... No unique index found for the referenced fiels of the primary
table.

 

[Rick Brandt]

I have assigned 2 fields as the primary key. I can link one of the
fields to another table but not the other. I keep getting the
following error message... No unique index found for the referenced
fiels of the primary table.

Drag both fields at once.

 

[potter]

Tried it but now the edit relationships window is asking which field to
relate within the table. I only want to relate it to one field. right? It
won't let me.

 

[Tom Ellison]

Dear Potter:

Is this table the foreign key table in the relationship you're creating, or
the dependent table?

If it is dependent on another table, then the keys to this table are
irrelevant to the relationship. A dependent table doesn't even require
there to be a primary key.

If it is the foreign key table, then you must create the relationship on all
the columns that form this key (or any other unique key you wish to define.)
Relationships do not have to be on the Primary Key, but should be on some
Unique Key.

Tom Ellison

 

[potter]

thx Tom... I still don't get how I can join the 2 tables. the 2 columns
(Market_Sector and Industry_Sector) combined are the primary key or unique
key. My goal is to join my tbl_Market with tbl_industry. Industry_Sector
columns exist in both tables. How do Join these tables again?

 

[Tom Ellison]

Dear Potter:

There are fundamental facts about relationships. Every relationship is
one-to-one, one-to-many, or many-to-many. The most common relationship, by
far, is the one-to-many relationship. It has a table on the "one" side (the
foreign key table) and a table on the "many" side, the dependent table.

Without knowing which kind of relationship you want it is difficult to help
you. If you are developing a one-to-many relationship, then it will be
necessary to know which table is foreigh and which is dependent.

If you don't know what these terms mean, I would suggest you make a study of
them before proceeding. Either the concepts will be familiar to you, but
the terminology new, or perhaps you don't have the fundamentals.

In the most common sense, JOINing two tables is a way of representing an
already defined relationship within a query. There are other JOINs,
especially non-equi JOINs, but this is quite advanced. Somehow I doubt
you're in that territory yet.

For your two tables, Market_Sector and Industry_Sector, please state what
columns are the Primary Key of each, and how they are related. Perhaps then
I can understand and help effectively.

Tom Ellison

 

[potter]

It is a one-to-many relationship. The "one" side is tbl_Market sector withe
the 2 columns (Market_Sector and Industry_Sector) as the unique key. The
"many" side is tbl_Sector. It also has a Primary Key (Industry_Group) JOINing
to another table.

That help explain what I am trying to do? By the way - I bought Access 2003
Inside out. It's helpfuls but I still don't get how to join tables if a
primary key uses multipal columns.

 

[Tom Ellison]

Dear Potter:

The table on the "many" side of a one-to-many relationship (your tbl_Sector)
must have a pair of columns that match the 2 column unique key
(Market_Sector and Industry_Sector) in the table on the "one" side of the
relationship (tbl_Market). Those columns must match the columns in
tbl_Sector that are the unique key you described (Market_Sector and
Industry_Sector) or you must have some other unique key to tbl_Sector on
which this relationship exists.

The unique key on the foreign table is essential in order for it to be on
the "one" side of a one-to-many relationship. The uniqueness on one or more
columns is what makes it a "one"-to-many relationship. That is, there is
only one row in this table that matches any number (many) of rows in the
other table.

Does this describe what you have? If you do not have another unique key to
tbl_Sector, then you must have some pair of columns in tbl_Market that match
them. Otherwise, you have not designed so as to have such a reletionship.

Relationships are not an accidental occurrance. You design them in when you
design the tables. You should create such relationships as you design the
tables. So, your question should not be a mystery to you at this time. Is
it? Do you have such a relationship designed and defined? Are you just
having trouble writing the query, or is there a failure in design behind
this?

Tom Ellison

 

[potter]

Tom - You are correct. in assuming I have many things to learn.

So if I assign the 2 combined columns as my primary key in tbl_market "one"
I must have both of the columns as my foreign key in my "many" tbl_industry?
I thought I could use one column as a foreign key my "many" table. Seams
like a bunch of lines between tables. why not just leave all the data in one
table? I know it will be slower if data repeats but at least all the data I
need is in one row or record. I can always find it! that is the goal...to
find and use the data... Trying to figure out these keys are killing me.

BTW - In my case I am not worried about typos because I always import this
127 column text file and append it to my large table. No manual date entry
need.

 

[TedMi]

If your primary key is a compound on two fields, then you MUST relate both of
the fields to fields in the dependent table. Them's the rules.

 

[Tom Ellison]

Dear Potter:

You are correct that the table on the "many" side of a one-to-many
relationship does not require that all the columns in its primary key (or
other unique key) be used in the relationship. Indeed, it is not always the
case that the column(s) in the "many" table are indexed at all.

However, all the columns of the table on the "one" side of the relationship
must together be unique (when taken together), so a unique index (including
a primary key) is required. It is required not only for uniqueness, but
because it gives good performance to have this index. It is used whenever
you JOIN to it using this relationship.

"Why not leave all the data in one table?" Well, where shall I begin:

- The information in the set of columns forming what is properly the table
on the "one" side of the relationship will be repeated many times. If you
should ever need to change anything, such as just correcting a spelling, you
would have to enter that correction many times. Perhaps you expect to just
make changes in the spreadsheet and then import it, in which case this is
likely what you mean to do anyway. But it doesn't have to be that way.

- It is not uncommon to have a hierarchy of tables in one-to-many
relationship. Consider the organization of a country. It has many states.
Each state has many counties. Each county has many townships. Each
township consisits of many properties. Each property may have many
residents. Each resident has several bank accounts. Each bank account has
many deposits and withdrawals. Putting all that into a spreadsheet would
result in repeating state, county, township, property, resident, and bank
account just to show a list of deposits and withdrawals. It is proper to
have a key that does this, but do you need to repeat all the other
information about the state (state capitol city name, governor's name, etc.)
many millions of times in order to show every deposit or withdrawal made by
every resident? I don't think so.

Databases are organized, very highly organized, and according to a science
(or is it a mathematics?) This has been developed and tested over much of
my lifetime, and there are reasons for why it is done the way it is done.
It would take a very good chapter in a book to even get started to explain
this. You can buy the book and read it, or you can take my word for it, or
you can suffer the consequences of doing it poorly. If you stay with it,
you'll eventually see what I have been trying to explain. I can only
recommend strongly that you learn this by research before you learn it the
hard way.

Tom Ellison

 

[mnature]

If you have all of your information in an Excel file, then there is an
excellent way of determining how to set up your tables. First, assume that
you will just set up one table, that has all of the information that exists
in the Excel file. Then carefully look at your columns (Excel) which are now
fields (Access). Some of those columns/fields will have duplicated
information. Whenever you have duplicate information, you should probably
pull that information out into a separate table. For instance, if you have
names (of people, things, or companies) that are repeated, then you could
have a tblNames containing fields like NameID, Nametxt, Nameaddress,
NamePhone. You would make NameID a primary key, which is what would be
referenced from your first table. So, you would have NameID in two tables,
linked in your relationships chart. The tblNames would contain only
information that actually relates to the Names. All of your tables should be
created because you want to separate and group information, based upon a
logical grouping of that information. Once you have separated out all of the
repititious information into their own tables, then your initial table should
show just your primary information, with just one primary key to uniquely
identify that information, and other links that are not keys, but just links
to the other tables containing information.

If you are not happy using this type of format, you would probably be better
off using Excel or Word to display your information.