Tips before starting - how to display several data of one owner?

[behedwin]

I have pretty much only done one larger build with access.
Now im starting to preper to redo everything, build it from scratch and make it a bit better from all the stuff i´ve learned so far.

But before i start id like some tips on how to design the tables best.

Currently i have quite large tables, with 10-20 columns.
But im now thinking of breaking them down and have for example one table called "Phonenumber" and then only have one phonenumber stored in that table. Another table called "Person" where i store names. Then i can set up so that one person can have several phonenumbers.

Is this a good thing, to set it up like this?


But how can i present it in a form.
How to show a persons name and the phonenumbers. What if one person have 1 number and another have 10
Is there a good way?

 

[jmacleary]

Hi there. To answer your question directly, yes it is a good thing, exactly because of the example you quote. Without getting all technical, this is called normalisation (you may want to google this (look for 'how to do normalisation') and read up a bit - it will be well worth your time as getting the data structures right is essential to a good design.
To display the phone numbers, look into the use of subforms (basically you could have a form that displays a persons name, etc. and within that form a subform that would display all the phone numbers for that person. Google 'access subform' and you will find a couple of good articles at the top of the list.

Good luck with your project.

 

[behedwin]

Thank you.
Ill look into this.
I like the idea of having it a bit more structured.
However i am a bit afraid of losing myself with all relationships that are needed when deviding up data in several tables.

Another question about relationships.
Can i skip to setup table to table relationships.
and only work with relationships in different querys that i use?

 

[jmacleary]

OK. It is a bit daunting at first, but I think you will find it becomes pretty straightforward. Most if not all of your tables (at the moment you would only have 2) will have a simple relationship. In this case, assign a unique key for each person in the person table, then use this key in the phonenumber table. If I understand your question correctly, there is no reason why you cant join your tables in a set of queries and use those queries to retrieve your data, but it really is a case of choosing whether to use the tables directly, or via a query.

 

[behedwin]

Great.
I prefer to use queries. just wanted to know if i could do that and just manage relationships in queries instead than joining up all relationships between tables.

My current project have become so large and i have just added functions ontop of each other untill i feel that im loosing control of things.
So i really need to start over and make it a bit cleaner.

Lucky this is very fun tho

Only wish it was possible to somewhat simply run a access database online, so i could access it everywhere from any computer in a simple way.
but that i have not figured out yet... guess i need to look at other solutions then and im not really done with access to leave it just yet