Results 1 to 5 of 5

Thread: Link two fields

  1. #1
    Join Date
    Nov 2004
    Posts
    18

    Unanswered: Link two fields

    VB6, Access

    What I have...(1 table) (these numbers arent hard-coded, just to explain what I need it to do)
    ThisClaimNumber ThatClaimNumber
    CN5005..............CN5006
    CN5005..............CN5007
    CN5005..............CN5008
    CN5005..............CN5009

    What I need...
    I need to know how to add new records based on ThatClaimNumber field. (ie ThisClaimNumber is what is referenced in code, but I need to link together) Simply put after code is executed I need the same table to look like this...
    ThisClaimNumber......ThatClaimNumber
    CN5005..................CN5006
    CN5005..................CN5007
    CN5005..................CN5008
    CN5005..................CN5009

    CN5006..................CN5005
    CN5006..................CN5007
    CN5006..................CN5008
    CN5006..................CN5009

    CN5007..................CN5005
    CN5007..................CN5006
    CN5007..................CN5008
    CN5007..................CN5009

    CN5008..................CN5005
    CN5008..................CN5006
    CN5008..................CN5007
    CN5008..................CN5009

    CN5009..................CN5005
    CN5009..................CN5006
    CN5009..................CN5007
    CN5009..................CN5008

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's a cartesian join. Google that and you should get plenty of examples for whatever database you're using.

    But uh... it's pretty rare to need to do this. What is the real world application for this table, perhaps there is a more elegant way?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Posts
    18
    scenario:
    1. on a claims form
    2. have a grid that I can add codefendants to (attached to claim numbers)
    3. have option to view codefendants in grid
    4. for example say I was on claim number 1
    5. want to add claim number 2 (another codef)
    6. then want to add another claim number (say 3) (again another codef)
    7. now I am on claim number 1, and have claim number 2 and 3 in grid
    8. I want to view claim number 3 in grid
    9. form gets loaded with claim number 3 info.
    10. problem is here (i don't have claim number 2 nor 1 associated with claim number 3) (not in grid)
    Last edited by McF; 12-07-05 at 13:01.

  4. #4
    Join Date
    Nov 2004
    Posts
    18
    can't be a cartesian join, a cartesian join uses two tables where I have but one.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, so if "claim1" is associated with claim2 and claim3, you would like to pull up claim3 and see it associated with claim2? Kind of strange, but meh... *shrug*

    As to the cartesian join, yes you do need two datasources. Most database platforms allow you to join a table to itself through the use of an alias (often referred to as a self join).

    IE:

    SELECT t1.field1 As FirstField, t2.field1 As SecondField
    FROM yourTable t1, yourTable t2
    ORDER BY 1, 2

    Note the omission of any sort of "JOIN" indentifier...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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