• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Access Tables


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

  • 4
  • 2
1 Solution
You will
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
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

naufalAuthor Commented:
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?

<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
naufalAuthor Commented:
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.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now