|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
relating two tables of similar data type with other tables & querimany 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 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. -- Show quotePC Datasheet Your Resource For Help With Access, Excel And Word Applications resou***@pcdatasheet.com www.pcdatasheet.com "PJ" <P*@discussions.microsoft.com> wrote in message news:CCEB3712-EB05-44D2-B568-A530EC30A941@microsoft.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. > > -- > PJ 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? Show quote "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 > resou***@pcdatasheet.com > www.pcdatasheet.com > > > "PJ" <P*@discussions.microsoft.com> wrote in message > news:CCEB3712-EB05-44D2-B568-A530EC30A941@microsoft.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. > > > > -- > > PJ > > > 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: Show quote > 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 >> resou***@pcdatasheet.com >> www.pcdatasheet.com >> >> >>"PJ" <P*@discussions.microsoft.com> wrote in message >>news:CCEB3712-EB05-44D2-B568-A530EC30A941@microsoft.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. >>> >>>-- >>>PJ >> >> >> 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 -- Show quote-Larry- -- "PJ" <P*@discussions.microsoft.com> wrote in message news:CCEB3712-EB05-44D2-B568-A530EC30A941@microsoft.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. > > -- > PJ  |
|||||||||||||||||||||||
Other interesting topics