Database design - PLEASE HELP!

[wanderliz]

I have a client list currently in excel that is becoming far too unwieldy but I have been trying to figure out how to turn the relevant flat table into an access database using the tuotorials on MS Office but I still can't get it right. Can someone please please please tell me how I could organize the following information:



It is basically a contact list, with company name, contact name and contact details (phone address email etc). There are a few hundred companies, with about a thousand contacts in total, so each contact is unique (primary key). I'm quite comfortable with that part and it seems that the contacts template would work quite well for that, however in addition each individual contact is coded with between 1-10 categories. This is to denote which information each client is willing to receive. The categories are ALL (meaning will receive everything) TECH, MINING, BANKING, INSURANCE etc. About half are coded ALL, and the rest have 2-10. Currently in the excel spreadsheet Ive done this by setting up 10 columns titled category 1, category 2, category 3 etc. and then filling in however many columns are relevant for each contact. This is where I'm really struggling with Access to understand a) what sort of table to put the categories into and b) how to define the relationships. Any help would be so greatly appreciated!!!

[ajetrumpet]

IMO, I would use one table in access. Categories can be set up the same way, one per column as BOOLEANS. use labels and checkboxes to mark them on your entry form. Then on your reports, use one column that specifies all categories relevant and use nested IIF() functions to concatenate the word descriptions of the categories that are marked.

a seperate table with category names as records might also be useful, but if they are only present to tag alone with the contact' records then there might not be a need for that.