If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
relating two tables of similar data type with other tables & queri
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. -- PJ |
#2
|
|||
|
|||
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 www.pcdatasheet.com "PJ" wrote in message ... 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. -- PJ |
#3
|
|||
|
|||
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" wrote: 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 www.pcdatasheet.com "PJ" wrote in message ... 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. -- PJ |
#4
|
|||
|
|||
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 PJ wrote: 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" wrote: 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 www.pcdatasheet.com "PJ" wrote in message ... 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. -- PJ |
#5
|
|||
|
|||
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 -- -Larry- -- "PJ" wrote in message ... 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. -- PJ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Understanding Primary Keys | Khai | Database Design | 3 | January 20th, 2005 09:47 PM |
Need help linking 2 tables for entering new & editing existing rec | scott | General Discussion | 2 | January 18th, 2005 10:09 PM |
How do I print the details view | David | Running & Setting Up Queries | 5 | August 28th, 2004 12:17 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |
Two Tables need same Data Type | Allison | Database Design | 0 | May 18th, 2004 04:26 PM |