Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Washington, District Of Columbia, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2010: connecting tables against multiple fields

    I have a regions table.

    Region Country
    Asia Vietnam
    Asia Japan
    Asia Korea
    APEC Vietnam
    APEC Japan
    APEC Korea
    WH Canada
    WH Mexico

    Then I have a success table

    Company COuntry1 Country2 Country3 Country4 Country5

    I want to run a query by Region that checks for members of that region against all 5 of the Success table Country Fields, and I admit to not being sure where to even start on this. (do I have to run 5 separate queries?) Ideally, I'd like to pass the Region parameter from a form. Help please.

    Alysha

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Alysha,

    I know you have this marked solved but have you considered that your success table is not "normalized"?

    You should really set up the Success table with only 2 fields: Company & Country. You would then set the Key Field to be both Company & Country to prevent duplicates.

    This will greatly simplify your queries as well as being better database design. As a side benefit you can have more than 5 countries for each company.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    Washington, District Of Columbia, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks RG for the suggestion. I'm not sure how to implement as this is a list from Sharepoint that I routinely import into Access (can't link because queries run too slowly) to run reports against based on ever changing criteria (table really has about 45 fields, some with fun multi-select). You're right that the results are not normalized, but my intent was try to group them on the success' unique id in the final report. As it stands, it is possible for there to be multiple records for the same company to the same country. Typically when I try to run a make-table query from the original SP list, I have fields that cause errors. At this point, we're limping along until we implement Salesforce later in the year. All that said, if you have other suggestions, I'm all ears. :-)

Posting Permissions

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