I am trying to track what words my students know. I have 4 tables, but I am stuck on how to make them relate to each other.

I have about 5000 words. I have about 50 tests. Each word can be on multiple tests. I have a list of students. I am calling a table "presentations" - this represents one word shown to one student on a date and a yes/no result.

I want to know which word is on which test for data entry - I have worksheets for each test that I made in Word and I want to be able to easily find the words that are on the test in the database, so I am assuming that a form will help me do this.

Here are the tables I have set up:

words:
wordID
wordNAME

students:
studentID
lastNAME
firstNAME

tests:
testID
testNAME
wordID

presentations:
date
studentID
wordID
result

I'm using IDs because I am under the impression that it is easier to manage a database this way, but I really want to be able to see the NAMEs when I am entering data.

How do I associate one word with multiple tests? One student with multiple words?

I hope this makes sense!

Thanks for taking the time to look at this.

Dana

Dana,

I think you need to make a "Many to Many" relationship.

First take out WordID out of Tests.

Second Remove the Presentations table. (Make a Query to accomplish you seeing when they took it.)

Add Date and Result to the Student Table.

Make a new Table that has "Word ID" and "Test ID" in it. (when making a relationship the many symbol should be on this side of join for both the Tests table and the Word Table)

Make another Table (although you could very well add it to the other new table) That has "StudentID" and "WordID" And make sure that it also has the Many Symbols on its side.

That should give you the correct result althought I Could be Wrong.

Thanks for your help!

I have set up all the tables and relationships you suggested, but now I don't know how to enter the words so that I can associate the tests with the words and the students with the words. I have data in all the primary tables, but I am not too sure what to do with the junction tables.

Thanks!

Dana

The Many to Many relationship tables do not need any data in them.

Make sure they do not have any Primary Keys.

You should be able to now make a query based on those fields and it should show up in form view which tests have the words and which students have had which words.

Give it a try you will want to make a query for each of those (2)tables.

Let me know how it turns out.

Use the IDs, which would be autonumber fields, and then when you create the look up fields, select LastName, FirstName for example, and the look up field will Add the ID or autonumber field automatically. Then the name will display, and not the number. When you build a report, though, remember to take the name from the parent table, and not the look up column from the child table. This will prevent the number showing up where there should be a name.

Structure:

tblWord
WordID - PK - autonumber
Word - text

tblTest
TestID - PK - autonumber
TestName
etc

tblWord_Test
Word_TestID - PK - autonumber
WordID - FK
TestID - FK
etc

tblStudent
StudentID - PK - autonumber
LastName - text
FirstName - text
etc

tblPresentation
PresentationID - PK - autonumber
StudentID - FK
PresentDate - Date
Result - text

tblPresent_Word
Present_WordID - PK - autonumber
PresentationID - FK
WordID - FK

This structure will allow you to keep a list of words, and a list of students. It will allow you to log the words used in a test, the presentations presented by each student, and the words used in those presentations.

Did this help?



mmcdonal

Also, when you do the look up from tblPresentation to tblStudent, add a column to the query:

FullName:([LastName] & ", " & [FirstName])

This will display "Smith, John" in one column instead of just "Smith" after you make your selection.

Bound Column: 1
Column Count: 4
Column Widths: 0";0";0";2"


mmcdonal