Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003

    Unanswered: Help with FORM Design & relationships

    I have a simple database with 3 tables. 1 table has insurance agents, 2nd table has the policy information for the applicant, 3rd table has the history for the applicant.

    I want to create a form that displays the applicant and the agent . Problem is that it is possible for 2 agents to be on one application.

    How do I design the query or the relationships to get the infomation I need?

    Thank you

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    You can make the agents a separate table and relationally join it (via the autonumber or other ID field in the main table). Just add the corresponding ID field as an integer in the agents table.

    Or you can make 2 agent fields in your main table (although this wouldn't be ideal - if a third agent is added, then you must do some work (ie. add fields/revise form) whereas setting it up relationally, you don't need to do anything)

    As far as designing the form with a relational setup, it's a simple subform (probably set to continous). Try a couple examples of these (after you've setup proper relationships) and you'll get the hang of it. I personally like to put the ID field (for the agents relational table) in the subform and set the default value of it: =Forms!MyMainFormName!MyAutoNumberField. The subform wizard works well in MSAccess for setting up subforms once your relationships are established.

    If you need more help, pages 6 or 7 in the code bank have some helpful tips on design.

    You'll need to come up with a table structure design first though (we don't know all your requirements), then send a picture of it with your relationships and we can then offer you advice. Providing the table structure is setup correctly, form design then becomes fairly easy.

    As far as advice on the table structure, if there's information which would cause unneccessary duplication in your main data tables (such as agent), that information should most likely go into a relational table linked by an ID (or primary key) field to the main data table.

    It sounds like you've got a general idea. Just create the table relationships and post a snapshot of them. If you need further help, MSAccess 2007 has a lot of great examples. Otherwise 2000/2003 has the Northwinds database to look at for relationships.
    Last edited by pkstormy; 03-27-10 at 03:22.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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