Populate Lookup Tables?

[Bonifes]

:banghead:I have problems understanding lookup tables, especially how to populate them with data. I'm working on an exams database, and have many such tables...

Table for Students
Table for Subjects
and a lookuptable for studentsubjects

Table for Classes 1, 2, 3, 4
Table for Subjects Eng, Math, Geo, Chem... 11 subjects in all
and a lookuptable SubjectClasses

Having inserted data for all subjects and all classes, should I then go ahead and Insert data for SubjectClasses? There will be 44 items!

Even more appalling, studentsubjects table will have (no of students * 11) for my small school, it will be 1,760

Forgive my being a beginner, but give me ideas!

 

[jdraw]

You may wish to work through this tutorial to get an understanding of database design before getting into Access nitty-gritty.

Good luck.

 

[namliam]

What you are discribing for studentsubjects is a Junction table, not a lookup table.

May indeed be a lookup table...

Having 1.700 records inside a single database is peanuts, I have databases handling millions of records at a time. Why is this a problem for you?

you can write some VBA to mass fill your table or use excel to multiply the data if you prefer... If the problem is down to a data entry problem?

 

[Bonifes]

Thanks Xperts
My concern comes from my lack of prior knowledge. I'm really wondering whether such data should be entered manually, computing the possible combinations. Thought it necessary to reply as I delve into further learning through your Tutorial. Thanks, I'll come back.

 

[David R]

You should only fill the table as needed. SubjectClasses is probably superfluous, as what you really want is StudentSubjects, right? With a field for Classes (1,2,3,4) embedded?

Read up on normalization (I think you said in another thread you've started to do so). Your linking table will store when a student has a specific subject at a specific class/period/block, right? Only difficulty will be if you are trying to import existing data from an Excel spreadsheet or something, but that is certainly possible with a little more information.

 

[Bonifes]

Yeah, It is as well a challenge I'm undertaking as I build the DB and learn. It is not a job I MUST complete. But I've been thinking about it for such a long time to warrant getting up to the challenge.

For better understanding, I have those four classes (many) and Students (many) I therefore created a table to relate them. In the same Classes, there are different subjects... Maths Class 1, Maths Class2, Maths Class3, Maths Class4, Eng Class1, etc. My class is not Period... It is a Level. You call them Grades? I understand the language/naming difference

From your point, I understand that I don't need to fill the link table.. Good to hear, I have not tested the DB.

Here I've taken a snapshot of my updated relationships. I'll clean the tables of unnecessary fields, which I'd linked wrongly.

 

[Bonifes]

Hi, I've updated the structure of the database as per help in this forum. Could I hear your comment on the new structure? You could follow the main thread here:
....forums/showthread.php?t=257315

Thanks all, as we complete on the design and move on to queries, forms and completing the DB.

 

[spikepl]

From your naming I smell lookups in Tables. In short - don't. http://access.mvps.org/access/lookupfields.htm It will confuse the crap out of you in no time. Lookups in forms are fine - use them there.

Say, if you need some item like examType when displaying ExamResults, make a query that shows what you need. So name what you are storing appropriately - in this case, in table ExamResults, examtypeID, and not ExamType as you have now.

 

[Bonifes]

Thanks SP. But I've been careful noit to use lookup fields. The naming is not a p[roblem, the data type is numbers and relates to the ID fields.

Here I've attached the DB. Could someone helpme with a query on which to build the forms?

I have here attached my expected reportcard for each student