Designing tables

[123shailesh]

want to create a database for our college examination cell. I wanted to know hwo I can create such a table:

RollNo. |------Subject1------|-----Subject 2-------| .....
-------| Theory |Viva| Total |Theory |Viva | Total |.....

There are around 6 subjects.
I want it soemthing like subject1.theory, subject1.viva etc

I know that is not exactly possible in MS Access, so could anyone tell me the best way to do it. I somehow wanted the subcolumns to appear as part of the subjects. (as we can do in excel)

I thought of naming them as sub1_theory, sub1_viva....... but not too happy with it.

any help would be appreciated.

 

[kabz]

so why do you just put your fields for your table as above:Subject1_Theory,Subject1_viva

that's 100% right Excel and Access are two different animals,most of the things you do in excel canot be done in Access

Why not happy about that,it could be a simpler way of doing it

 

[stopher]

I thought of naming them as sub1_theory, sub1_viva....... but not too happy with it.

Don't forget that these are only names for the columns in your table. You can display anything you like in your reports and forms. Users should not have direct access to the tables so will never have to see what you've called them.

That aside, I think your table structure should be:

RollNo__Subject___Theory__Viva
001____Subject1___78_____89
001____Subject2___67_____78
001____Subject3___56_____89
002____Subject1___77_____88
002____Subject2___88_____99
etc

This is more normalised. I think there's even a case for putting Viva (whatever that is) and Theory in the same column.

With this structure you can do lots of stuff like find average for subject 1, list averages by RollNo etc.

Also, you can still show it in the way you asked by doing a pivot query on this structure and adding nice titles at report level.

hth
Chris

 

[Rabbie]

First of all do a search on normalisation and read that. Remember that alhough the tables won't look like Excel you can make them look like spreadsheets by using queries and forms in datasheet mode if thats what you want.

A normalised design will let you expand easily from 6 to seven subjects without difficulty

It would be helpful to see what you really want from the database before suggesting a design but repeating groups are a definite no-no.


Roll no

 

[boblarson]

I'll chime in as well. Just as everyone has mentioned, check out (and read until you understand) normalization. It will save your butt in the end (no pun intended). If you are moving into Access after using Excel, you have to make the effort to realize that they are two totally different things. Access is a RELATIONAL Database and the simplistic way of saying it is you need to think long and narrow instead of short and wide.

If you are going to use repeating fields (Subject1, Subject2, etc.) then you might as well just stay with Excel because doing so in Access totally defeats the purpose of using a relational database.