MVF vs Lookup Fields vs Multiple Tables [Noob] (1 Viewer)

[jonnyboy81]

Hi Guys, I'm a pretty novice user of Access. Thought I knew what I was doing until I tried to created a complicated contact database.


I'm trying to set up a DB with hundreds of contacts, which I want to be able to sort via two main fields. Functions and Specialisation.

'Functions' is more or less a department field but as there is crossover I need to be able to add multiple 'Functions' values for each contact.
'Specialisation' narrows down departments into sections but again as there is crossover so there needs to multiple 'Specialisation' values.

I originally thought having two tables 'TblFunction' & 'TblSpecial' listing all the values I need (linked via the Function PK) and having two fields on my 'Contacts' Table (where I store Names, Phone Numbers etc) as a Lookup Fields would be all I need but I have since learned this causes a number of problems when running queries etc.

Is there a better way to store multiple values in a field or am I better off trying to break each table down into more tables?

I had thought about putting in multiple fields as check boxes but it works out as way too many fields and became pretty unruly pretty quickly.


Any advice no matter how small, even to point me in the right direction, would be greatly appreciated.

J

p.s. sorry if I didn't explain this very well!

 

[CazB]

sounds like you have a couple of many-to-many relationships there, so you'll probably be best off using linking tables to build the relationships you need...

have a look here http://www.databasedev.co.uk/many_to_many_example.html to see how that can work....

 

[jonnyboy81]

Thanks for the help.

I've read over the article (and a couple of others about Many-to-Many relationships), and I can see how they work in examples like Albums (or Books), where both Artist and albums reference each other: Artist can have multiple Albums, Albums can have multiple Artists, but I'm not sure how to apply it to my database.

Although I do want each contact to be able to have multiple 'Functions' and 'Specialisations' I'm not sure if I should create two extra tables (and 2 extra Junctions) assigning a list of contacts to each 'Function' and 'Specialisation'

In other words, I want One Contact to have Many Functions and Many Specialisations but I'm not sure if I need One Function to have Many Contacts. Would I better off just using 2 Lookup Fields for my Contact table?

If I were to create the extra tables, should I be entering a new record for each new contact? Should I enter each contact via a form that updates these extra tables? I think I'm confusing myself even more :/


Thanks for your help

 

[Minty]

If there is a lot of crossover with functions and specialities it might be easier to simply list them All in one table with an additional field that is an flag (F or S or any other type of description you may want).

Then you can store these in a table called contactExpertise with the contact ID and as many entries from your expertise/function table as you want.

 

[jonnyboy81]

Thanks for the reply Minty,

When you say list them all, do you mean list out each Speciality individually as a field on the new table? Then have a True/False value for each?

There are about 12 Functions, each with around 10 Specialites, so roughly about 120 fields, is that okay?

At the moment I have a specialty table with each specialty listed as a record with it's own SpecialtyID and each Specialty record has it's relevant function listed as a FunctionID from the Function Table.

What would be the best way to store these multiple values (for each contact) in the new contactExpertise table?

 

[Minty]

Ah okay - so are you saying any function could have any speciality? If so you may as well store the 2 fields in your separate expertise table. It would look like

ExpertiseID, ContactID, Function, Speciality

Each contact can have as many functions with a Speciality as you want.

If you want to restrict a functions available speciality then then you would need your additional junction table to restrict your options.

 

[jonnyboy81]

Thanks Minty,

Not sure that I was too clear there. Each Speciality has a specific Function, so the Function Table has a one-to-many relationship to the Speciality Table.

Then each Contact could have multiple Functions, with multiple specialties attached to each function.

Anyway I'll try setting up the Expertise table and see how I get on, I'm not sure I'm up to this level yet :/

 

[Minty]

Persevere - once you begin to get the idea - you'll start normalizing things by default. If you are properly sad that is...