Many to many problem 1

[ISTodd]

Hi I have two tables and I think the relationships are correct however things aren't happening the way I was hoping. I have three tables

TblStudent
StudentID PK
LName
FName


TblStudentAssignment
Score
StudentID PK
AssignmentID PK

TblAssignment
AssignmentID PK
AssignmentDate
AssignmentSubject
AssignmentName

I would Like to create and add assignments in (1 form) and then in a (2nd) form List all the sudents and just add the score for the particular shown assignment...

It sounds easy enough but its not working.........

Is this a form problem or a relationship problem??? Thanks for any help you can offer - Todd

 

[dhookom]

This isn't much different than the Orders (Student), OrderDetails (StudentAssignments), and Products (Assignments) tables in Northwind.

A main form could be based on the student table with a subform based on the student assignments. You would select an AssignmentID from a combo box and then enter the score.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

ISTodd

Great job Todd! You have designed the many-to-many correctly. Only tweak would be to consider adding "DateHandedIn" to TblStudentAssignment (which could be different from the AssignmentDate), and a Comment / Memo field to TblStudentAssignment to capture any comments about the report -- yours (hmmm, very similar to John smith's report) or theirs (dog ate my first draft, and my borther reformatted my disk on the second draft).

What specifically is not working?

Did you...
- Make sure the StudentID and AssignmentID is defined correctly in TblStudentAssignment? If you use AutoNumber in TblStudent, then it should be a "Long Integer" for TblStudentAssignment. Ditto for the AssignmentID. Look at Data Types in Access help if required.
- Define the relationships using the Relationship GUI tool in Access? From the menu, "Tools" -> "Relationships". Add the three tables. Then click an drag the primary key from one table to the foriegn key in the ralated table. A popup window will appear to edit the relationship. Enable "Ensure Referential Integrity" -- this means that the assignment and the student must exist before you can capture info in TblStudentAssignment.

Is your problem presentation?
To test the design, have sample / test data, and create queries using the Query Builder. Provided you create your query after defining the relationship, Access will create the correct relationships when you add related tables. Ditto for forms with main form and subform.

Here are two ideas in presentation...
Define a main form based on TblAssigment.
- Add an unbound combo box using the combo box wizard to find a (Assignment) record on the form.

Create a form based on TblStudentAssignment and ...
- make AssignmentID hidden
- Make StudentID into a combo box
SELECT StudentID, LName & ", " & FName
FROM TblStudent
Order By LName, FName
Boundcolumn: 1
ColumnCount: 2
ColumnWidths: 0"; 1.5"
Then embed this subform into the main form

This form / subform will allow you to easily enter the marks for specific assignments.

Define a main form based on TblStudent.
- Add an unbound combo box using the combo box wizard to find a (Student) record on the form.
Create a form based on TblStudentAssignment and ...
- make StudentID hidden
- Make AssignmentID into a combo box
SELECT AssignmentID, AssignmentSubject & ", " & AssignmentName
FROM TblAssignment
Order By AssignmentSubject, AssignmentName
Boundcolumn: 1
ColumnCount: 2
ColumnWidths: 0"; 1.5"
Then embed this subform into the main form

This form / subform will allow you to easily review the marks for specific students.

Richard

 

[ISTodd]

Hi Richard Thank you for your detailed response....

The 1st qustion question you asked was "What specifically is not working".

The answer to that question is: After checking to make sure my tables are set-up the way you described and adding test information in the student and Assignment tables - I can't get my query to display any records... and I cant enter a score into the query. I'm having similar problems with my forms...

When I set up the query, do I need to include specific fields?

I will again try to set up my form in the way you described, but it seems like I am missing something small but essential.

Thanks again - Todd

 

[willir]

Easy one first...

and I cant enter a score into the query.

There are certain requirements where a query involving more than one table need to meet before the results can be updated. I suspect you realize that you want to update TblStudentAssignment. Review the following article...
Harnessing the Power of Updatable Queries

I can't get my query to display any records

Are you getting an error or just no results?

Start off simple. Work with one table in the query. The results will be updatable provided you include the primary key.

You can enter some data here if you wish.

Then, work with two tables in the query. I did a quick setup with your spec's
SELECT TblAssignment.AssignmentSubject, TblAssignment.AssignmentName, TblAssignment.AssignmentDate, TblStudentAssignment.StudentID, TblStudentAssignment.Score, TblStudentAssignment.HandInDate
FROM TblAssignment INNER JOIN TblStudentAssignment ON TblAssignment.AssignmentID = TblStudentAssignment.AssignementID
ORDER BY TblAssignment.AssignmentSubject, TblAssignment.AssignmentDate;


Results were as expected...
[tt]
AssignmentSubject AssignmentName AssignmentDate StudentID Score HandInDate
English Book Report 1 3/2/2005 3 80 3/1/2005
English Book Report 2 3/15/2005 3 83 3/13/2005
Math Assigment 1 3/1/2005 2 75 3/1/2005
Math Assigment 1 3/1/2005 1 85 3/2/2005
Math Assigment 2 3/7/2005 2 78 3/8/2005
Math Assigment 2 3/7/2005 1 83 3/6/2005
[/tt]

I then added the third table, TblStudent, and swapped StudentID for LName...
SELECT TblAssignment.AssignmentSubject, TblAssignment.AssignmentName, TblAssignment.AssignmentDate, TblStudent.LName, TblStudentAssignment.Score, TblStudentAssignment.HandInDate
FROM TblStudent INNER JOIN (TblAssignment INNER JOIN TblStudentAssignment ON TblAssignment.AssignmentID = TblStudentAssignment.AssignementID) ON TblStudent.StudentID = TblStudentAssignment.StudentID
ORDER BY TblAssignment.AssignmentSubject, TblAssignment.AssignmentDate;

Results were as expected...
[tt]
AssignmentSubject AssignmentName AssignmentDate LName Score HandInDate
English Book Report 1 3/2/2005 LeGuin 80 3/1/2005
English Book Report 2 3/15/2005 LeGuin 83 3/13/2005
Math Assigment 1 3/1/2005 Brooks 75 3/1/2005
Math Assigment 1 3/1/2005 Assimov 85 3/2/2005
Math Assigment 2 3/7/2005 Brooks 78 3/8/2005
Math Assigment 2 3/7/2005 Assimov 83 3/6/2005
[/tt]

If you used names the names of the tables and fields as provided, switch your query to SQL view (with the query open in design view, from the menu, "View" -> "SQL")

If you don't see the "INNER JOIN ... ON", then you may not have defined the relationships correctly.

Note that the above tables will not allow you enter records because the essential primary keys are not included.

If you are still having problems, post your SQL statement.

Richard

 

[ISTodd]

Richard,

Thank you for your help regarding this issue. Your detailed instructions made it very easy to figure out my mistake. I intend to use your suggestions elsewhere in my database since I need help with (presentation).

I still am having a problem with one part of this whole thing. I want a main form to be student information and my Subform to be a list of all available assignments based on the subject- That will allow me to enter the score....

Smith, John Spelling


Test 1 (enter score)
Test 2
Test 3
Homework 5/1/05
etc


I plan to work on it this weekend (since I really am a novice) with the suggestions you sent in your second e-mail.

Again - Thank you for looking at my table design and giving me these suggestions!

Todd