Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: Relationship Woes!!

    Hello, here's my problem, and I'm not too sure what the solution would be.

    I'm creating a trade directory, and I would like the business on this directory to be listed under more than one category. I currently have my table setup to accept one (one-to-many relationship) category, but when I want to add another category (located in another field) it won't display both.

    Currently:

    Table 1 Table 2
    Cat_num -> Business_Cat1

    What I'd like

    Table 1 Table 2
    Cat_num -> Business_Cat1
    Business_Cat2

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    I'm not sure I fully understand where you are up to but here's what I think you are trying to say.

    You have a table (lets call it tblCustomer) which contains a list of customers and you have a couple of fields which allow you to link to the business category. The customer number is the primary key. So your table looks something like this:

    tblCustomer

    CustomerNo....Cat_No1......Cat_No2
    124................1.................2
    125................1.................3


    You also have a trading category table which looks like this (Business_Cat is the primary key):

    tblCategory

    Cat_No....Cat_Description
    1.................HiFi
    2.................TV
    3.................Video

    I now suspect you have linked (or are tyring to link) Cat_No1 and Cat_No2 in tblCustomer to Cat_No in tblCategory.

    The problem with this kind of design is it's difficult to do things like "list all customers by category which should look like:

    Category..... Customer
    1.................124
    1.................125
    2.................124
    3.................125

    A more appropriate design would be to use three tables:

    tblCustomer

    CustomerNo
    124
    125

    tblCustCat

    CustomerNo....Cat_No
    124................1
    124................2
    125................1
    125................2

    tblCategory

    Cat_No....Cat_Description
    1.................HiFi
    2.................TV
    3.................Video

    Then CustomerNo in tblCustCat is related to CustomerNo in tblCustomer and Cat_No in tblCustCat is related to Cat_No in tblCategory. In this design it is very easy to create the querys/reports you are looking for.

    One point to note. In the form design you will have a main form and a sub-form. E.g. the main form could be customer and the sub-form will allow multiple entries of Cat_No for that customer.

    HTH
    Howey

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •