Why do all my Relationships show as Indeterminate ?

I have 6 tables

tblMain
tblMain_aux_data
tblChild_1
tblChild_2
tblChild_3
tblChild_3x

I have the relationships linked up but they all show as
indeterminate??

tblMain should be in a 1:1 relationship with tblMain_aux_data

tblMain should be in a One-to-Many relationship with tblChild1, 2 & e

tblChild_3 should be in a one-to-many relationship with tblChild_3x

No data yet except in tblMain

I do Access only on occasion so am rusty.

Can't I tell Access what the relationship should be... or does it
matter?

thanks for any help.

Hi.

I have 6 tables

tblMain
tblMain_aux_data
tblChild_1
tblChild_2
tblChild_3
tblChild_3x

I hope those aren't the real names. ;-)

I have the relationships linked up but they all show as
indeterminate??

This happens when the tables are linked or the foriegn key in the child
table is based upon a column (or columns) that isn't the primary key in the
parent (tblMain) table or doesn't have a unique index constraint.

Can't I tell Access what the relationship should be

No. You must first set up the table structures correctly, and then Access
will show you what the relationship is when you drag the column names from
one table to the other in the relationships diagram window.

or does it
matter?

If you care about referential integrity and therefore, data integrity, then
yes, it matters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


wrote in message
oups.com...
I have 6 tables

tblMain
tblMain_aux_data
tblChild_1
tblChild_2
tblChild_3
tblChild_3x

I have the relationships linked up but they all show as
indeterminate??

tblMain should be in a 1:1 relationship with tblMain_aux_data

tblMain should be in a One-to-Many relationship with tblChild1, 2 & e

tblChild_3 should be in a one-to-many relationship with tblChild_3x

No data yet except in tblMain

I do Access only on occasion so am rusty.

Can't I tell Access what the relationship should be... or does it
matter?

thanks for any help.

You need to create relationships that involve:

1.) Fields of the same data type (For example, Autonumber --- Long Integer)
2.) One of the fields is uniquely indexed (either set as a Primary key, or a
unique index has been created first).

Which field is designated as the primary key in your tblMain table, and,
what is it's data type? The foreign keys, in each related table, must be the
same data type. For your 1:1 relationship, you can join two primary key
fields, but only one of them can be the Autonumber data type if you are using
this type of key.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

" wrote:

I have 6 tables

tblMain
tblMain_aux_data
tblChild_1
tblChild_2
tblChild_3
tblChild_3x

I have the relationships linked up but they all show as
indeterminate??

tblMain should be in a 1:1 relationship with tblMain_aux_data

tblMain should be in a One-to-Many relationship with tblChild1, 2 & e

tblChild_3 should be in a one-to-many relationship with tblChild_3x

No data yet except in tblMain

I do Access only on occasion so am rusty.

Can't I tell Access what the relationship should be... or does it
matter?

thanks for any help.

On 7 Apr 2007 14:37:21 -0700, wrote:

I have 6 tables

tblMain
tblMain_aux_data
tblChild_1
tblChild_2
tblChild_3
tblChild_3x

Wooops... this looks like either misleading table names, or bad design. You
certainly should NOT have three tables which differ only in the table name.
What do the four "child" tables represent? Do they have identical sets of
fields (not a good idea) or are they independent tables?

I have the relationships linked up but they all show as
indeterminate??

tblMain should be in a 1:1 relationship with tblMain_aux_data

tblMain should be in a One-to-Many relationship with tblChild1, 2 & e

tblChild_3 should be in a one-to-many relationship with tblChild_3x

No data yet except in tblMain

I do Access only on occasion so am rusty.

Can't I tell Access what the relationship should be... or does it
matter?

How did you create the relationship? And which fields did you use in the
relationships window? A relationship is indeterminate if there is no unique
index on either field involved in the join. Typically one would join the
Primary Key (which by definition has a unique index) of the "one" side table
to a non-unique foreign key field in the related table. Do you have primary
keys set?

John W. Vinson [MVP]

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

1.) Fields of the same data type (For example, Autonumber ---
Long Integer) 2.) One of the fields is uniquely indexed (either
set as a Primary key, or a unique index has been created first)

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hi David,

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

Did you read the rest of my post, where I wrote, where I wrote:
"For your 1:1 relationship, you can join two primary key fields, ... "

Okay, so perhaps I should have wrote:
"For your 1:1 relationship, you MUST join two primary key fields, ... "

Since 1:1 relationships are fairly rare, compared to 1:M relationships,
writing it as you suggest might cause more user frustration if they want to
create a 1:M relationship and they tried using uniquely indexed fields on
both sides of the join.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"David W. Fenton" wrote:

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

1.) Fields of the same data type (For example, Autonumber ---
Long Integer) 2.) One of the fields is uniquely indexed (either
set as a Primary key, or a unique index has been created first)

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Okay, so perhaps I should have wrote:
"For your 1:1 relationship, you MUST join two primary key fields, ... "

Before I get called on the carpet, let me amend that last statement, as
follows:

Okay, so perhaps I should have wrote:
"For your 1:1 relationship, you MUST join two UNIQUELY INDEXED fields,
.... "


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Tom Wickerath" wrote:

Hi David,

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

Did you read the rest of my post, where I wrote, where I wrote:
"For your 1:1 relationship, you can join two primary key fields, ... "

Okay, so perhaps I should have wrote:
"For your 1:1 relationship, you MUST join two primary key fields, ... "

Since 1:1 relationships are fairly rare, compared to 1:M relationships,
writing it as you suggest might cause more user frustration if they want to
create a 1:M relationship and they tried using uniquely indexed fields on
both sides of the join.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"David W. Fenton" wrote:

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

1.) Fields of the same data type (For example, Autonumber ---
Long Integer) 2.) One of the fields is uniquely indexed (either
set as a Primary key, or a unique index has been created first)

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Dear Tom:

You can join on any column, or set of columns, whether it is a primary key
or not. You can join on any key that is unique or not unique. If the table
is on a 1 side of a relationship, the key will be unique, and possibly a
primary key. If on the M side, it will not be enforced unique (it certainly
can be unique by choice or coincidence in individual cases - there can even
be 0 rows on the M side of a relationship). In an M:M relationship, neither
is unique, and therefore an autonumber is NEVER used in an M:M relationship.
Such tables can have an autonumber columns, they just won't be involved in
the relationship.

The key on the 1 side of a relationship is unique, but need not be an
autonumber. Any unique key of 1 or more columns will work. And, again, any
unique key will do. It need not be the primary key, although it quite often
is.

I quite commonly create entire, complex databases without using any
autonumber columns, using the natural keys as the primary keys. I have not
found there to be any disadvantage in doing this for fairly small databases,
by which I mean any less than 2GB, the Jet limit. Indeed, I can get more
data into my databases by having fewer columns and fewer indexes.

I am working hard on a paper to present a discussion on the difference
between natural key relationships and surrogate (autonumber) key
relationships. I know this has been a hot button issue for some time, and
my recent findings will be surprising. I favor the advantages of both
camps, and am preparing to present a proposal that will give the advantages
of both systems, indeed, which will give additional advantages available
with neither approach.

See you and all my MVP freinds soon in the MVP private newsgroups.

Tom Ellison
Microsoft Access MVP (6 years)


"Tom Wickerath" AOS168b AT comcast DOT net wrote in message
news
Hi David,

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

Did you read the rest of my post, where I wrote, where I wrote:
"For your 1:1 relationship, you can join two primary key fields, ... "

Okay, so perhaps I should have wrote:
"For your 1:1 relationship, you MUST join two primary key fields, ... "

Since 1:1 relationships are fairly rare, compared to 1:M relationships,
writing it as you suggest might cause more user frustration if they want
to
create a 1:M relationship and they tried using uniquely indexed fields on
both sides of the join.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"David W. Fenton" wrote:

Tom Wickerath AOS168b AT comcast DOT net wrote in
:

1.) Fields of the same data type (For example, Autonumber ---
Long Integer) 2.) One of the fields is uniquely indexed (either
set as a Primary key, or a unique index has been created first)

That should be "one or both of the fields is uniquely indexed", as
you need both for a 1:1 relationship.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Sat, 7 Apr 2007 20:29:28 -0500, "Tom Ellison" wrote:

I am working hard on a paper to present a discussion on the difference
between natural key relationships and surrogate (autonumber) key
relationships. I know this has been a hot button issue for some time, and
my recent findings will be surprising. I favor the advantages of both
camps, and am preparing to present a proposal that will give the advantages
of both systems, indeed, which will give additional advantages available
with neither approach.

I'm very much looking forward to seeing this, Tom! Thank you for taking on
this daunting task!

John W. Vinson [MVP]

Dear John:

It has been 2 years in the making. Perhaps I'll have it this summer. I'm
thinking of sharing some partial results with my peers for review. I'll
make an announcement in the private MVP newsgroup soon I think.

Tom Ellison
Microsoft Access MVP


"John W. Vinson" wrote in message
...
On Sat, 7 Apr 2007 20:29:28 -0500, "Tom Ellison"
wrote:

I am working hard on a paper to present a discussion on the difference
between natural key relationships and surrogate (autonumber) key
relationships. I know this has been a hot button issue for some time, and
my recent findings will be surprising. I favor the advantages of both
camps, and am preparing to present a proposal that will give the
advantages
of both systems, indeed, which will give additional advantages available
with neither approach.

I'm very much looking forward to seeing this, Tom! Thank you for taking on
this daunting task!

John W. Vinson [MVP]