Learn how to a build a cloud-first strategyRegister Now


Access Tables

Posted on 2007-08-12
Medium Priority
Last Modified: 2013-11-05

I have a table in my database which has following fields.

Nepotism Link1
Nepotism Link2
Nepotism Link3
Other information.....

Now Nepotism Link1, 2,3... takes the values of The ID's of other records in the database and tells their corresponding relationship via Nepotismtype1,2 and 3....

Now what I want is, that if for a record ID1 I have NepotismLink1=3, NepotismLink2=6 and Nepotismtype1=brother and nepotismtype2=sister, since the reverse would be true for record with ID's 3 and 6 as I have entered in NepotismLink1 and 2, I want this information to go directly in records 3 and 6.

Only ID's that will be in the database will obviously be entered in any of the Link value, so database should have a knowledge of that records.....

Thanks and Regards

Question by:naufal
  • 4
  • 2
LVL 39

Expert Comment

ID: 19680421
You will
LVL 39

Expert Comment

ID: 19680425
You will have a difficult time doing what you want because you table is not normalized.  One you redesign the table so it is normalized, it will be a lot easier.  

Maximizing rows (records) and minimizing columns (fields) is the way databases are designed to work and is called table normalization.   To demonstrate this point, notice that Access and all other databases limit the number of columns (fields) but rows (records) are limited only by storage space.  These are the two tests that I apply for normalization:

1. Are there horizontal lists (columns, fields) that can be converted to vertical lists (rows, records).  If I do what will it gain me?  (Usually reduced storage, memory requirements, increased speed and the ability to work with the data easily) What will I lose? (Usually nothing)

2. If I have nn number of similar fields, is there a possibilty that I might later have nn+1 number of those fields?

A very common table design error is something like:
RecordID   Type1    Type2   Type3    ...
1                23
2                              77
3                 17                       7

Which should be designed as:
RecordID   Type   TheValve
1                1           23
2                2           77
3                1           17
3                3             7

Here is a great real world example of the importance of normalization:

A great turorial on Data Normalization

Database normalization basics in Access

Understanding Relational Database Design

Expert Comment

ID: 19680510
I agree. In plain words you'd have a tuple table consisting of :


then when you add one nepotism you could add the reverse one as another record
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 19680551
Yes I definitely agree that database is not normalized, but I do not have the option of redoing it at the moment. But I do have a question. I can have another table of nepotism type, and though not ideal, but I will have to choose all type which is a text (sister, brother, wife etc.... ) as a primary key, because the user will not like the input of numbers, if i choose a number to be a primary key of the table. Or is there any way where I can have on my input form the number as an input, but it also has the text description of the number along with it in the menu e.g. 1-Sister, 2-Brother .... which will only input the number in the database but put the description on the form alongside?

Secondly regarding the nepotism link: it will be basically another ID (of some other record/person). I cannot have another table for those ID's. So they will still have to be under the same table i.e. Personal data table. Would this work?

LVL 39

Accepted Solution

thenelson earned 300 total points
ID: 19680646
<Or is there any way where I can have on my input form the number as an input, but it also has the text description of the number along with it in the menu e.g. 1-Sister, 2-Brother>

In the table design view, click on the lookup tab.  Select display control: "combobox", row source type: "value list". In Row Source put: "1,Sister,2,Brother"..., Bound column: "1",  Column Count: "2", Column Widths: "0"

Now Sister, Brother, etc will be displayed but 1,2, etc. will be stored.

<it will be basically another ID (of some other record/person). I cannot have another table for those ID's. So they will still have to be under the same table i.e. Personal data table. Would this work?>

I don't think so.  Foreign key to primary key references require separate tables.

I would recommend you do some reading on how to design and use databases.

Go to Access help>contents>Getting started with Microsoft Access>Get started with Microsoft Access 2000>Using a database for the first time.  If using Access 2002+, Just enter "Using a database for the first time" (with the quotation marks) into the help window.   That is a good way to get started.

You can enter "Training" in Access help to get listings of training options.

Here are a bunch of free or low cost tutorials on learning Access:

Beginner to Intermediate :
34 Free lessons on Access and other links:

18 Free lessons on Access:

Links to Free MS help like explanations of various procedures, properties, etc. from the horse's mouth:

13 Free multimedia lessons on Access

14 Free multimedia lessons on Access

1 free cd or 14 free days online

9 Hours of Interactive Training $99 for DVD

Beginner to Advanced:
Free  lessons on Visual Basic For Application and other links:

Free trials, $149/year for all lessons

Author Comment

ID: 19696448
Thanks thenelson, It works!

If you can please just guide me one thing.... When copying a database on one other computer, some of the programming I have done regarding pop-up of my combo boxes, does not work.... Is there anything which i need to know about copying the databases to other locations and/or computers.

LVL 39

Expert Comment

ID: 19696877
Over 90% of the time if Access works on one computer but not another, it is missing references or references of the wrong version.  In the Visual Basic Editor check under tools > references for any reference that says "missing".  Fix that.

If you don't have the word "Missing:" on one of the references on the  computer causing the problem, carefully check the date, time and size of every referenced file on the problem computer against the date, time and size on a computer without problems to make sure the versions are the same.  With any that are different, copy the problem computer file to another folder (in case changing it causes other problems) and copy a "good" one to the problem computer.

To avoid that problem try to store all the reference files in the same relative UNC path (such as: C:\Program Files\Microsoft Office\OFFICE11\ADDINS).  Also use an application launcher to deploy the database (my favorite free one is Little Setup Builder http://www.ammasw.com/LSB_Intro.html because it is so quick and easy to use) to make sure the correct referenced files are where they are supposed to be.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question