Table and Relationship Design

Posted on 2005-05-04
Last Modified: 2008-03-17

I have a database that I need a little help configuring. This table is used to keep track of cable records for telephone numbers. The main table has these fields

Table: Main

Phone Number

My problem is that each phone number needs a unique pair on a cable. I don't know how to configure the table or releation ship.

In addtion, what can a set up as far forms where I can add a cable and specify that this cable X amount of pairs.  

Thanks for any help that you can provide.


Question by:Henrypb
    LVL 65

    Expert Comment

    by:Jim Horn
    >each phone number needs a unique pair on a cable
    Can you explain this in greater detail?

    Author Comment

    Thanks for the rapid response. Here is an examle of what I am working with.

    Phone number 8513030  gets to the customer on cable 1 pair 5.
    Phone number 8513001  gets to the customer on cable 1 pair 10.
    Phone number 8516000  gets to the customer on cable 4 pair 25

    I want to be able to restrict users from assigning the same cable and pair to a phone number.
    LVL 65

    Expert Comment

    by:Jim Horn
    In that case, in your table just make Phone Number the primary key, and in the forms that allow data entry before you insert/update a record you'll have to run a query to see if there are already any cable-pair combinations that match what your user entered, and if so do not allow the insert/update.

    The relationship between cable-pair 'pair' and phone number cannot be modeled solely in table design.

    Hope this helps.
    LVL 9

    Expert Comment

    If you just want to prevent more than 1 combination of Cable/Pair happening then you could join themtogether as a unique index under


    LVL 39

    Accepted Solution

    Your in luck.  I am familiar with Access and have worked as phone interconnect tech.

    The simplest way to do what you want to do is have a table with two fields:
    FIELD 1:
    flield name: Cable-Pair (or Cable/Pair or however you want to specify it.)
    data type: text
    key field
    format: "00-00"  I would require two digits to provide for proper sorting
    you would enter 01-05, 01-10, 04-25 (or 01/05, 01/10, 04/25)  or if you want, you could identify them as 01-blu, etc

    FIELD 2:
    field name: phone number
    data type: text
    field size: 12
    format: "999-999-9999"
    you can make it indexed no duplicates since I doubt you would assign the same phone # to two different line pairs.

    Author Comment

    Thanks everyone for the replies. I am going to go with thenelson's approach. Thanks again.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Updating recordset in Access VBA 13 43
    MsgBox 2 29
    message box in access 4 21
    Access 2013 combo box not working 3 11
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    730 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

    15 Experts available now in Live!

    Get 1:1 Help Now