Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Relationships Question

    I've created simple DB's with Access before that did not require multiple tables for reports so now I have what is probably a simple problem i can't seem to figure out.

    I'm creating a DB to generate insurance proposals. In it I have 17 tables ranging from client information to carrier data to plan benefits. I cannot get them to relate to each other to generate a report or to cross-populate on forms.

    For example, one form will be used to populate insurance carrier contact data and another to populate client data. I want to do two things; have a dropdown on the client form to select the carrier and then generate a report link the two together.

    I do have primary keys (autonumber) on all tables. and have linked them in the relationships window but they don't cross-pollinate. Additionally, I've created queries but nothing gets generated on the report.

    It's probably stupid and easy, but I'm pulling out my hair here.

    Thanks in advance for any help.

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    On the client form, the dropdown rowsource needs to be set to a query, which you can either type in or use the query builder to design. Just something like "SELECT ID, Name FROM InsCarrier" would give you the ID (or whatever you called your PK field) and the Name field from your insurance carrier table.

    Your queries/reports aren't goingn to show any data unless the related fields are populated correctly. It's hard to say what you're doing incorrectly without seeing it, but to do it correctly, it should be something like this:

    Insurance Carriers Table
    (PK) ID - 3
    Name - Company A

    Client Info Table
    (PK) ID - 54
    Name - Client M
    (FK) ICID (Long datatype) - 3 -> which relates to the IC table for Company A

    So if you wanted to see everyone in Company A on a report, your query would be like:
    SELECT [InsuranceCarriers].Name, [Clients].Name FROM [InsuranceCarriers] INNER JOIN [Clients] ON [InsuranceCarriers].ID=[Clients].ICID WHERE [InsuranceCarriers].ID=3
    Last edited by jmahaffie; 09-28-09 at 14:03.

Posting Permissions

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