Limited Number of Values in Lookup Column

[lacey]

I don't know a lot about access so I need help, etc. etc.!

I have a database for scheduling students' for tests. They can take up to six tests in a day. There are about 80 different tests that they can take.

In my table, I created columns titled Test1, Test2, Test3, etc. They are lookup columns and I chose to enter my own values, putting in the tests titles for the values in each column.

When I add these drop-down lookup fields onto the form, it will only display 37 of these values. When I go back to the table and select "edit list items," it shows that it did cut the list off at 37, even though originally it allowed me to enter all 80-ish titles.

Anyway. It appears that there are limited values you can have in a lookup column, though after doing a lot of searching online I can't find anything to indicate that is true. Anyone here know if that is true?

It seems to me that it would be smarter to set this up with two different tables, storing the reg info in one table and the test titles in another table. However, I am having a hard time figuring out the relationship aspect of this solution and how to make it pull up the correct values for queries/reports as well.

Am I on the right track here? Does anyone have a better solution? If this is the right way to go, can anyone help me with the relationship aspect of it? Again, I really don't know anything about relationships... I have attempted to create other databases using them correctly and have had limited success. Many tutorials/articles I have found online usually just end up confusing me :banghead:

Thanks bunches!

 

[boblarson]

1. These should not be COLUMNS in a table, but should be ROWS.

2. Having a bit of trouble figuring out exactly what you have but a sample table set up is:

tblStudents
StudentID - Autonumber (PK)
FName
LName


tblTests
TestID - Autonumber (PK)
TestDescription

tblStudentTests
StudentID - Long Integer (FK)
TestID - Long Integer (FK)
TestDate
TestResult

Then Students table is the basis for the main form and then the subform would be based on tblStudentTests and the TestID selection would be a combo box to select which test is being referred to/taken, etc.

 

[jdraw]

Seems you have 3 tables (an maybe more)

Students--->StudentScheduledForTest<---Tests

tblStudents
StudentId PK
StudentFirstName
StudentLastname
other student specific info

tblTests
TestId PK
TestName
other test specific ino

tblStudentScheduledForTest
StudentId
TestId composite PK
ScheduledDate
ActualDateTaken
other info specific to Student and test on ScheduledDate

These will help with your tables and relationships
Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming


Good luck with your project

After posting I see Bob has responded.

 

[lacey]

Thank you both for your responses!

Okay... this is getting me further but still a little lost. I have created the three tables Bob recommended. I have no idea what "long integer" means or how to make the StudentID & TestID this format in the tblStudentTest. I am assuming this is an automatic occurance?

When I created the form based on the Students table, it did not bring the testID up as a combo box in the subform with the possible tests, but just as a text field. I did add a few of the test titles under "Test Description" on the tblTests, but I do not understand how to make that show up as a combo box on the subform. Help?

 

[lacey]

By the way, thanks for the video tutorials jdraw! I will definitely check those out.

 

[boblarson]

Here's a sample I wrote for you. Look at the relationships window and look at how the subform interacts with the main form (the Master/Child links set the student ID automatically in the tblStudentTests table) and the way the combo box is set up for selecting the tests.

Also, Long Integer is the default when you select NUMBER under datatype.

 

[lacey]

You are my hero! This makes so much more sense. Thinking it over more, it's actually pretty similar to another database I created last year and had to work through some similar issues. Being over a year later, I seemed to have forgotten a lot of the steps with the process. Seems to happen often since access isn't my main project at work.

Thanks so much for your help!