Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2010
    Posts
    14

    Unanswered: Table linking/relationship "How do I" question

    I will do my best to explain what I need to do here.

    I have a contact table that contains contact code, names, addresses, type of contact (client, broker, former client, etc) and if they are a broker which client they are a broker for (using the contact code in a look up list). I have a second table that contains the client name, account name(s) and number(s). End result is I need to do a mail merge using the broker info (name, address, etc) and the client account name(s) and number(s).

    What would be the best way to link the two tables (or create a relationship between) the two tables so that a query will pull up the broker info and the related client info?

  2. #2
    Join Date
    May 2010
    Posts
    601
    I think it will be best if you post a sample of your database with test data. Nothing confidential.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jul 2010
    Posts
    14
    How should I do that? I am trying to export to an access database but it's not letting me.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Try compacting the database then compress it into a ZIP file
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jul 2010
    Posts
    14
    Sample dbase should be attached.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Vallynmar View Post
    What would be the best way to link the two tables (or create a relationship between) the two tables so that a query will pull up the broker info and the related client info?
    There is a difference between a relationship and a query join (what I think you mean by linking).

    I think your question is actually "what sort of join should I use?" in which case you sound like you want to use a Left Outer Join.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2010
    Posts
    601
    Took a look at your database.

    I think the issue is how to make a join on the field [Accountant/Broker to Whom] which is a multi-value field.

    I would change to to use a junction table.

    See attached example
    Attached Files Attached Files
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    Jul 2010
    Posts
    14
    unfortunately I can't seem to get winzip to open the attachment.

  9. #9
    Join Date
    May 2010
    Posts
    601
    I recreated it again.

    See if this one works
    Attached Files Attached Files
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Jul 2010
    Posts
    14
    Hate to say this it's still not opening. It keeps telling me it doesn't appear to be a valid archive. Maybe it's because I'm using the evaluation version?

  11. #11
    Join Date
    May 2010
    Posts
    601
    The second ZIP was made using jsut windows. you can use Widnwos to uncpress it.

    Try this freeware:
    7 ZIP
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  12. #12
    Join Date
    Jul 2010
    Posts
    14
    So if I use this method how would I do my query? Would I need to add the ID number as a field to my account number table? Oh and thank you 7-zip worked
    Last edited by Vallynmar; 10-04-10 at 15:42. Reason: forgot a sentence

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to help with the file swapping business...

    The forum won't let you upload .mdb or .accdb files because of the potential of spreading malicious code. One method to exchange the files is zipping the file (as Boyd suggested). If you don't actually require the compression you can just change the extension to something benign like .txt. Given you guys are struggling, and I assume this is a teeny file, mebbe this could be something to try.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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