Access Tables

Posted on 2007-08-12
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
    LVL 39

    Expert Comment

    You will
    LVL 39

    Expert Comment

    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;EN-US;234208
    LVL 1

    Expert Comment

    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

    Author Comment

    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

    <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

    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

    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 because it is so quick and easy to use) to make sure the correct referenced files are where they are supposed to be.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now