Dear Sir or Madam,

 

I am constructing a table to hold multiple names and dates, what is the 

best way to construct this table.  

 

E.G. This table Holds 

1)Studies(Four Research Studies)

2)Names(Ten Research Professors)

3)Dates ( Due Dates, Assigned Dates, Completed Dates)

To make Input forms, report print outs easier to read, without having to 

write crazy queries- do have any surgestions in the construction of the 

table.

How about a Studies table, a Professors table, and a Studies_Dates

table.



The Studies table will store the Research Studies data.  Use an

autonumber StudiesID as the PrimaryKey.

The Professors table will store the info on the Professors. Use an

autonumber ProfessorID as the PrimaryKey

The Studies_Dates table will be the linking table between the Studies

and Professors tables.  It should store the ProfessorID, the StudiesId,

the three dates.  It should have it's own PrimaryKey.



Hope this helps







John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: Kenneth Mungwira [mailto:kennethmungwira@y...]

Sent: Friday, September 07, 2001 7:47 AM

To: Access

Subject: [access] Re: Construction Data Table





 Dear Sir or Madam,



I am constructing a table to hold multiple names and dates, what is the

best way to construct this table. 



E.G. This table Holds

1)Studies(Four Research Studies)

2)Names(Ten Research Professors)

3)Dates ( Due Dates, Assigned Dates, Completed Dates)

To make Input forms, report print outs easier to read, without having to

write crazy queries- do have any surgestions in the construction of the

table.

Thank you I will try it and see if it will work 

  John Ruff <papparuff@c...> wrote: How about a Studies table, a Professors table, and a Studies_Dates

table.



The Studies table will store the Research Studies data. Use an

autonumber StudiesID as the PrimaryKey.

The Professors table will store the info on the Professors. Use an

autonumber ProfessorID as the PrimaryKey

The Studies_Dates table will be the linking table between the Studies

and Professors tables. It should store the ProfessorID, the StudiesId,

the three dates. It should have it's own PrimaryKey.



Hope this helps







John Ruff - The Eternal Optimist :-) 





-----Original Message-----

From: Kenneth Mungwira [mailto:kennethmungwira@y...]

Sent: Friday, September 07, 2001 7:47 AM

To: Access

Subject: [access] Re: Construction Data Table





Dear Sir or Madam,



I am constructing a table to hold multiple names and dates, what is the

best way to construct this table. 



E.G. This table Holds

1)Studies(Four Research Studies)

2)Names(Ten Research Professors)

3)Dates ( Due Dates, Assigned Dates, Completed Dates)

To make Input forms, report print outs easier to read, without having to

write crazy queries- do have any surgestions in the construction of the

table.

Kenneth,



John is definitely on the right track here. We're definitely dealing with at 

least one many-to-many relationship between at least two tables. Access can 

only establish a many-to-many relationship between two tables by using a 

linking table that contains both keys of the linked tables as forign keys. 

Both forign keys in the linking table serve as the linking tables composite 

primary key. Does that make sense? If so, so far so good.



But we need more information. Can one faculty member teach many classes? Can 

many classes be taught by the same faculty member? And what about your 

"Completed Date?" Is that completed by a faculty member or by a student? If 

it's completed by a student then we'll also need a students table with a 

StudentID primary key related to a StudentStudies linking table with a 

composite primary key consisting of StudentID and StudiesID keys from those 

two tables, respectively.



In any event, when John says that the Studies_Dates table must "have its own 

primary key", he means that it must have a composite primary key consisting 

of BOTH the ProfessorID and StudiesID fields. Thats OK if the "Completed 

Date" field means completed by professors, and not by students. These two 

keys in the linking table exist as forign keys to the ProfessorID field in 

the Professors table, and the StudiesID field in the Studies table. But we 

might need to bring a students table in the mix. What do you think?



Do you know how to establish a composite primary key, i.e. a primary key 

consisting of two or more table columns?



>From: "John Ruff" <papparuff@c...>

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Re: Construction Data Table

>Date: Fri, 7 Sep 2001 08:03:20 -0700

>

>How about a Studies table, a Professors table, and a Studies_Dates

>table.

>

>The Studies table will store the Research Studies data.  Use an

>autonumber StudiesID as the PrimaryKey.

>The Professors table will store the info on the Professors. Use an

>autonumber ProfessorID as the PrimaryKey

>The Studies_Dates table will be the linking table between the Studies

>and Professors tables.  It should store the ProfessorID, the StudiesId,

>the three dates.  It should have it's own PrimaryKey.

>

>Hope this helps

>

>

>

>John Ruff - The Eternal Optimist :-)

>

>

>-----Original Message-----

>From: Kenneth Mungwira [mailto:kennethmungwira@y...]

>Sent: Friday, September 07, 2001 7:47 AM

>To: Access

>Subject: [access] Re: Construction Data Table

>

>

>  Dear Sir or Madam,

>

>I am constructing a table to hold multiple names and dates, what is the

>best way to construct this table.

>

>E.G. This table Holds

>1)Studies(Four Research Studies)

>2)Names(Ten Research Professors)

>3)Dates ( Due Dates, Assigned Dates, Completed Dates)

>To make Input forms, report print outs easier to read, without having to

>write crazy queries- do have any surgestions in the construction of the

>table.

>