Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    relationships, queries, & populating combo boxes (2003)

    I am confused about the right way of setting up some relationships and queries. While I understand the general principle of 1 to many etc, and can create the relationships in the relationships window, could someone put me on the right track or point out where I'm going wrong? Ultimately I am wanting to have combo boxes on a form where I can select a record from the list, being populated by (some of the fields from) records in one of the tables.

    I have some tables designed, and relationships set up: tblEmployers (with primary key EmployerID) has "1 to many" relationship with tblClientPlacements (with foreign key EmployerID) - each employer can have many client placements. tblClients (with primary key ClientID) has 1 to many relationship with tblClientPlacements (with foreign key ClientID) - each client could have many client placements. tblClientPlacements has other fields in it related to the placements. The first thing for me to check is, can I set up tables in this way, given there is effectively a many to many relationship between employers and clients?

    I have a second similar "many to many" relationship. tblEmployers has a 1 to many relationship with tblEmployerInteractions (foreign key EmployerID). tblConsultants (primary key ConsultantID) has a 1 to many relationship with tblEmployerInteractions (with foreign key) and tblEmployerContacts has a 1 to many relationship with tblEmployerInteractions (with foreign key).

    I have a form based on a query containing all the fields from tblEmployers that will record the numerous details about the employers. I intended setting up sub--forms (on separate tabs), one for "client placements" based on tblClientPlacements, the other for "employer interactions" based on tblEmployerInteractions. I have lost my way at this point, not being sure if this is even a possible approach, but if it is then what is the right way of creating the query or queries for this?

    Assuming that I can get this far, and have the sub-forms on my main form, then this is where I would want to populate combo boxes based on fields in (for example) the tblClients data. Specifically, I can create a field in a query based on the FirstName and LastName fields joined to make the Name field made like this: Name:[FirstName] & " " & [LastName]. Name would be what is populating the combo box.

    In the past I have often populated combo boxes with row source being a query, though these are not linked - don't have a relationship with - the main table of the database, they are simply there to populate the combo box. It has occurred to me that this is not necessarily the best way of doing things hence attempting this approach I'm seeking help with. I could be wrong, and that in fact that previous method is the only thing I can do. I trust this all makes sense and that someone could offer me some guidance?

    Regards, Roger

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: relationships, queries, & populating combo boxes (2003)

    Broadly everything you describe is correct.

    The subform for ClientPlacements (for example) is bound to tblClientPlacements. It includes the field ClientID from this table. Display this field as a combo , with its row source being a query (or sql statement) based just on tblClients. Include two fields, ClientID in the first column, and ClientName: [Firstname] & " " & [surname] as the second column, but sort on the ClientName (or just the Surname) . But set the width of column 1 to 0, and the ClientID will be hidden. (NB Name is a reserved word so shuld be avoid for fields, including calculated ones.)

    I attach a demo.

    Please ask further if you wish after looking at the demo.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships, queries, & populating combo boxes (2003)

    In addition to John's reply, see <post#=364,203>post 364,203</post#> for another example of how to handle data entry for a many-to-many relationship with a main form and subform.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships, queries, & populating combo boxes (2003)

    Thank you, John and Hans, this has been most useful, and I have the subforms working as I wanted now.

    Cheers, Roger

Posting Permissions

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