I have a table called ClientTBL in which I store information about our clients. There are some clients that I have to include medical records, emergency contact information, etc. (which would be a table with about 50-60 fields) but for about 90% of the clients I just need the name, address and phone numbers.

It would be easier for me to create the one gigantic table and seems to make sense.

I have two questions...
When I store a table with many null values, will this take up a great deal of storage space?
Will this slow down queries a great deal when I am doing mail lists, etc. when I am only looking at a few of the fields? Or will the queries only look for the fields listed int he query?

Thanks,
t

one giant table with lots of nulls will take up space - lots of it. it will also not only slow down your queries but cause them to be VERY PAINFUL (and sometimes impossible, depending on the complexity of the results you want) to create in the first instance.

what you need to do is "normalize" your data - you need a client table, a MedicalRecords table (with a ClientID in it to match them up) and a Details table (where you can have many contact details for each client, again linking it up with ClientID to the ClientID in the client table).

so, one client can have, for example, emergency contact, home contact, work contact. and if you need to add another you just add a new record to the Details table, rather than adding more fields in your client table.

hope that helps.

Thank you, wiklendt.

I think I have a handle on where I was going astray.

I apprecaite your help.

Tim

I agree with wiklendt. Also, to be seriously considered, lots of Access functions don't like nulls very well because of the way that nulls affect everything that tries to use them. You invite lots of remedial work to fix queries that blow up when something they try to touch contains a null.

On the other hand, I'm not sure how much extra space a table takes up when it has lots of nulls, depending on the data type of the null field. Sometimes a null doesn't cost you that much space. But it always costs you a lot of your time because of the extra attention to detail required to handle nulls in a way that doesn't eat your queries, formulas, and functions.