From: forest8 on
Hi there

I've searched through the database and have read about Relationships,
Primary Keys, and Foreign Keys. But I am still having problems with my
database.

I currently have a database that has several tables. The primary table is a
list of students where their student number is the primary key. (StudentID),
In each table, there is an Access created ID which is an autonumber.


Each table has this StudentID and the ID included. If ID is the primary Key,
I can create a relationship but the link between all the tables is the
StudentID. I can't create a relationship based on the StudentID.

Thank you in advance for your help.


From: Gina Whipp on
forest8,

Going to need to know a wee bit more information. All we know so far is
that you have a Students table, what are the rest of the tables? What are
you tracking, enrollment, attendence, grades...? You say , "I can't create
a relationship based on the StudentID.", but you don't say why you fell you
can't do that.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"forest8" <forest8(a)discussions.microsoft.com> wrote in message
news:5207ABC9-1399-414D-9E6D-739A3DD1C05A(a)microsoft.com...
> Hi there
>
> I've searched through the database and have read about Relationships,
> Primary Keys, and Foreign Keys. But I am still having problems with my
> database.
>
> I currently have a database that has several tables. The primary table is
> a
> list of students where their student number is the primary key.
> (StudentID),
> In each table, there is an Access created ID which is an autonumber.
>
>
> Each table has this StudentID and the ID included. If ID is the primary
> Key,
> I can create a relationship but the link between all the tables is the
> StudentID. I can't create a relationship based on the StudentID.
>
> Thank you in advance for your help.
>
>


From: forest8 on
Sorry for the lack of information in the initial question. I've been
frustrated by my inability to create relationships.

When I try to create a relationship, I seem to be creating an
"Indeterminate" relationship. I can't create a one to one or one to many
relationship. I get "Intderminate" relationship.

I'm tracking enrolment, grades, adds/drops, etc. There is also a survey
based on how the students have rated their courses/teachers. As well, there
is a table which tracks the frequency and subject of any meetings between
student and teachers.




"Gina Whipp" wrote:

> forest8,
>
> Going to need to know a wee bit more information. All we know so far is
> that you have a Students table, what are the rest of the tables? What are
> you tracking, enrollment, attendence, grades...? You say , "I can't create
> a relationship based on the StudentID.", but you don't say why you fell you
> can't do that.
>
> --
> Gina Whipp
> 2010 Microsoft MVP (Access)
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> http://www.regina-whipp.com/index_files/TipList.htm
>
> "forest8" <forest8(a)discussions.microsoft.com> wrote in message
> news:5207ABC9-1399-414D-9E6D-739A3DD1C05A(a)microsoft.com...
> > Hi there
> >
> > I've searched through the database and have read about Relationships,
> > Primary Keys, and Foreign Keys. But I am still having problems with my
> > database.
> >
> > I currently have a database that has several tables. The primary table is
> > a
> > list of students where their student number is the primary key.
> > (StudentID),
> > In each table, there is an Access created ID which is an autonumber.
> >
> >
> > Each table has this StudentID and the ID included. If ID is the primary
> > Key,
> > I can create a relationship but the link between all the tables is the
> > StudentID. I can't create a relationship based on the StudentID.
> >
> > Thank you in advance for your help.
> >
> >
>
>
> .
>
From: Gina Whipp on
forest8,

Help me out... I have listed a few tables based on your description. What
am I missing and where/what are the Primary Keys and Foreigh Keys? ith that
I can lead you in setting up Relationships.

tblStudents

tblEnrollment

tblCourses

tblTeachers

tblSurvey

tblMeetings

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"forest8" <forest8(a)discussions.microsoft.com> wrote in message
news:C4647781-EF5C-44CA-BEAF-8471CA918025(a)microsoft.com...
> Sorry for the lack of information in the initial question. I've been
> frustrated by my inability to create relationships.
>
> When I try to create a relationship, I seem to be creating an
> "Indeterminate" relationship. I can't create a one to one or one to many
> relationship. I get "Intderminate" relationship.
>
> I'm tracking enrolment, grades, adds/drops, etc. There is also a survey
> based on how the students have rated their courses/teachers. As well,
> there
> is a table which tracks the frequency and subject of any meetings between
> student and teachers.
>
>
>
>
> "Gina Whipp" wrote:
>
>> forest8,
>>
>> Going to need to know a wee bit more information. All we know so far is
>> that you have a Students table, what are the rest of the tables? What
>> are
>> you tracking, enrollment, attendence, grades...? You say , "I can't
>> create
>> a relationship based on the StudentID.", but you don't say why you fell
>> you
>> can't do that.
>>
>> --
>> Gina Whipp
>> 2010 Microsoft MVP (Access)
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors
>> II
>>
>> http://www.regina-whipp.com/index_files/TipList.htm
>>
>> "forest8" <forest8(a)discussions.microsoft.com> wrote in message
>> news:5207ABC9-1399-414D-9E6D-739A3DD1C05A(a)microsoft.com...
>> > Hi there
>> >
>> > I've searched through the database and have read about Relationships,
>> > Primary Keys, and Foreign Keys. But I am still having problems with my
>> > database.
>> >
>> > I currently have a database that has several tables. The primary table
>> > is
>> > a
>> > list of students where their student number is the primary key.
>> > (StudentID),
>> > In each table, there is an Access created ID which is an autonumber.
>> >
>> >
>> > Each table has this StudentID and the ID included. If ID is the primary
>> > Key,
>> > I can create a relationship but the link between all the tables is the
>> > StudentID. I can't create a relationship based on the StudentID.
>> >
>> > Thank you in advance for your help.
>> >
>> >
>>
>>
>> .
>>


From: John W. Vinson on
On Wed, 6 Jan 2010 11:49:02 -0800, forest8 <forest8(a)discussions.microsoft.com>
wrote:

>When I try to create a relationship, I seem to be creating an
>"Indeterminate" relationship. I can't create a one to one or one to many
>relationship. I get "Intderminate" relationship.

You will get an Indeterminate relationship if you are joining two tables on a
field, and that field does not have a UNIQUE index in either table.

Ordinarily one would join from the Primary Key of the "one" side table in the
relationship, to a foreign key in the "many" side table. The Primary Key is by
definition unique, and Access will create a unique index on it for you.
Creating a relationship and enforcing referential integrity will automatically
create a non-unique index on the foreign key field for you.

It sounds like you either haven't established primary keys, or that you're
trying to join on some other field or fields.
--

John W. Vinson [MVP]