Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 29th, 2007, 03:14 PM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default help setting up (what I think is) a simple db

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
Reply With Quote
  #2 (permalink)  
Old January 29th, 2007, 03:38 PM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

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.



Reply With Quote
  #3 (permalink)  
Old January 29th, 2007, 09:07 PM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old January 30th, 2007, 08:55 AM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

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.

Reply With Quote
  #5 (permalink)  
Old January 30th, 2007, 09:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old January 30th, 2007, 09:49 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting up a DB TJNevis ASP.NET 3.5 Professionals 1 September 13th, 2008 02:28 PM
Finding the DB Sailor.mdb of Beginning ASP DB book anna Classic ASP Databases 2 August 5th, 2006 01:13 PM
Setting Runtime DB connection from JSP ssivakumar76 BOOK: Beginning Java 2, JDK 5 Edition 0 June 16th, 2006 03:07 AM
Converting Access DB to Online DB eyal8r Access 5 December 6th, 2004 05:22 AM
Setting a DB value on Session Abandon bigmish Classic ASP Databases 6 July 19th, 2004 10:46 AM



All times are GMT -4. The time now is 01:31 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.