Table Design Query

[Josef]

how many fields can an access table handle ??

the fields in the table all relate to a specific sunbject [STUDENT], but
the length of the table is running up to 40 fields. i find this
excessive.

within thetable there are fields that can be broken down into smaller
tables, with the PK for each different table being the StudentID. this
would create smaller tables

any suggestions ??

cheers.

 

[Guest]

A table can hold 255 fields, so 40 seems reasonable to me.
hth

 

[Jeff Boyce]

Josef

An Access table with even 40 fields might be more than you need. Any chance
you can give us some idea of what types of data you are including in those
40? It may be that there's a more normalized design possible for your data.

 

[Josef]

Josef

An Access table with even 40 fields might be more than you need. Any chance
you can give us some idea of what types of data you are including in those
40? It may be that there's a more normalized design possible for your data.

student data
id / first / middle / last name / dob / gender
contact details / bh / ah / mobile
ermergency details / name / bh / ah / mobile
Mailing details, both permanent and mailing (addr1-3, city, state, zip,
country)

... so far, the above fields add up to 28 ...

.... then stat info that relates to each student ... 8 fields

.... then disability info for each student ... another 10 fields

.... then some comment / status fields .. 2 more here


i have looked at spitting the tables into:
[Student], [Stats], [Disability]

this is fine but i don't want to create 1-to-1 relationships, i don't
see the value in this other than the table is lengthy for my liking.

thx.

 

[Jeff Boyce]

Josef

Thanks for the additional information.

Does every student have "disability" information? If not, this would be a
reason to split out disability information to another table, related m-1 to
student, but only with as many rows as you have students with
disability(ies).

Are your "disability" fields related to Yes/No or Types of Disability?
Repeating fields (e.g., Type1, Type2, ...) are a sign of incomplete
normalization -- you could create a Disability table with only two fields,
StudentID and DisabilityTypeID, and a lookup table with DisabilityType.
This would remove the need for repeating fields. (i.e., one student can
have zero to many disabilities)

Do your "status" fields relate to Yes/No or Type Of fields? If repeating
fields ... (see above).

I don't understand your rationale for NOT wanting to create any 1-1
relationships.

 

[Jeff Boyce]

Josef

Newsgroup protocol suggests NOT including attachments. After all, would YOU
open an attachment from someone you don't know?

Besides, some of the folks following the 'groups use a slow connection, and
pay by the minute -- they'll likely not even look at your post, as
downloading the attachment costs too much.

If you'd like 'group readers to look over your structure, include a brief
description in your post -- e.g.

tblStudent
StudentID (autonumber)
FName
LName
DOB
...

tlkpDisability
DisabilityID (autonumber)
DisabilityType
Description
...

trelStudentDisability
StudentDisabilityID (autonumber)
StudentID (from tblStudent)
DisabilityID (from tlkpDisability)
DateNotified
...

(this is not intended as a recommendation, merely an example)