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

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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 :p
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top