relating two tables of similar data type with other tables & queri

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing a relational database to track student projects. It uses a
many to many junction table to link a student with a project, because over
time a student can have more than one project. This works fine in most
circumstances as most projects are associated with one student. There are,
however, exceptions.

I have 2 projects with 2 students each. When both students are in the Name
table with the same project ID link. Only one name is returned in queries and
reports. It has been suggested that a need a second name table [name2] with
identical data structure to be populated with the second student on the
project.. Name2 would link to the junction table using student ID.

Then I can select from both tables when creating queries or reports. Of
course on forms and reports there would be a sub report/form to contain the
data from table name2.

Problem 1: Because name2 has only 2 records, my queries trying to pull data
from both tables fail.

Problem 2: I can't seem to get subforms and subqueries to work either.

Does anyone have a suggestion?

Thanks for your ideas.
 
How about adding a student team table to your database. A student team would
be associated with projects rather than a student. A student team could be
one or more students. A student could be part of more than one team. A team
could work on more than one project. Your junction table then would combine
records from the student team table with the project table.
 
I have other tables tracking student demographics, including post-graduate
activities. I don't think a team approach would work in this circumstance.
Other Ideas?

PC Datasheet said:
How about adding a student team table to your database. A student team would
be associated with projects rather than a student. A student team could be
one or more students. A student could be part of more than one team. A team
could work on more than one project. Your junction table then would combine
records from the student team table with the project table.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


PJ said:
I am developing a relational database to track student projects. It uses a
many to many junction table to link a student with a project, because over
time a student can have more than one project. This works fine in most
circumstances as most projects are associated with one student. There are,
however, exceptions.

I have 2 projects with 2 students each. When both students are in the Name
table with the same project ID link. Only one name is returned in queries and
reports. It has been suggested that a need a second name table [name2] with
identical data structure to be populated with the second student on the
project.. Name2 would link to the junction table using student ID.

Then I can select from both tables when creating queries or reports. Of
course on forms and reports there would be a sub report/form to contain the
data from table name2.

Problem 1: Because name2 has only 2 records, my queries trying to pull data
from both tables fail.

Problem 2: I can't seem to get subforms and subqueries to work either.

Does anyone have a suggestion?

Thanks for your ideas.
Click to expand...
Click to expand...
 
PJ,

A student may have several projects, and several students may work on a
project... a classic many-to-many relationship. The standard approach is
to use an intermediate table for project enrollments, so your data
structure is something like:

tblStudents
StdID (PK)
Name
....


tblProjects
PrjID (PK)
Description
....


tblProjectEnrollments
PEID (PK)
PrjID (Foreign Key)
StdID (Foreign Key)
....

(tables joined on common fields). So, the third table will hold as many
records for each student as their projects, and as many records for each
project as the students involved.

This will solve all your problems.

HTH,
Nikos

I have other tables tracking student demographics, including post-graduate
activities. I don't think a team approach would work in this circumstance.
Other Ideas?

:

How about adding a student team table to your database. A student team would
be associated with projects rather than a student. A student team could be
one or more students. A student could be part of more than one team. A team
could work on more than one project. Your junction table then would combine
records from the student team table with the project table.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I am developing a relational database to track student projects. It uses a
many to many junction table to link a student with a project, because over
time a student can have more than one project. This works fine in most
circumstances as most projects are associated with one student. There are,
however, exceptions.

I have 2 projects with 2 students each. When both students are in the Name
table with the same project ID link. Only one name is returned in queries
and

reports. It has been suggested that a need a second name table [name2]
with

identical data structure to be populated with the second student on the
project.. Name2 would link to the junction table using student ID.

Then I can select from both tables when creating queries or reports. Of
course on forms and reports there would be a sub report/form to contain
the

data from table name2.

Problem 1: Because name2 has only 2 records, my queries trying to pull
data

from both tables fail.

Problem 2: I can't seem to get subforms and subqueries to work either.

Does anyone have a suggestion?

Thanks for your ideas.
Click to expand...
Click to expand...
Click to expand...
 
Hi PJ,

You've been caught in the "analyst denial" trap. I don't have enough
fingers and tows nor even years in my life to count the number of times I've
asked the client representative about the specifics of cases only to be told
"Usually it works this way". They perceive me as a large PITA when I rebut
that with "Tell me every case this functionality must handle even if its
only one in a million".

You have to design your applications to handle ALL of the situations that
can occur and that usually means designing to handle the most complicated
one-of-a-kind situation as if it were the rule. In this case, design your
schema as if All Projects had multiple students assigned. That implies a
many-to-many- relationship between Students and Projects with a
corresponding junction table.

HTH
 
Back
Top