Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    joining fields (97)

    I need to have two fields joined together. One is Location - this will have a pull down menu with the set choices. The next one is Division - each Location is in a division, either Central, West or North. When I pick a Location, I want the Division to automatically come up in the Division box. But I also need to be able to sort on both of these fields.

    They are going to be part of a form with all kinds of other fields on it. Right now I've started the main table with things like Call# (primary field), User Name, etc.

    My question is should I put Location and Division in a different table, in two different tables? How do I get them linked together, to show up on the form, and to be able to sort by?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: joining fields (97)

    I'm not sure what you're asking. If you want a pull down menu of choices, then yes, the values that populate that list should probably be in separate "lookup" tables, one for division and one for location. If the relationship is one division to many locations (that is, a location may only be in one division but a division contains many locations), then you can simply add a division id field to your location table and populate that with the primary key of the appropriate division from the division table. You relate the two tables on the shared key field, in this case division ID.

    I don't understand your remark about being able to sort on those fields. Why wouldn't you be able to sort on them? And are you talking about indexing them in the tables or sorting by them in a query or on a form?

    Forms and tables are two very different objects. Forms don't look like or behave like tables and vice versa. You design tables to hold data, period. You design forms to present data to users and to accept input from the users. It sounds like you're getting ahead of yourself and trying to design form features in tables, which isn't necessary. If you get the table design right, building your forms and the queries you will use to populate them is much easier.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: joining fields (97)

    Hi
    Thanks for the help. I think you're right, I was trying to jump ahead of myself. I understand so far what you're saying but still not sure how to do this.

    Let me try to explain again. The relationship is one division with many locations. I want the user to be able to start filling in the record and when they get to location, have a pull down menu to select a location. Then I want the division for that location to automatically pop up. So I need first to join the correct division with each location, then I need to be able to put that together with the rest of the fields in my main table so for each record a location can be picked, division automatically gets picked and it is joined with the rest of the record. I guess my problem is that I don't know where to get it joined together, and I don't know where to make the pull down menu.

    I may be making this harder than it has to be. Any help would be appreciated.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: joining fields (97)

    Well, one thing I would point out is that if location and division are joined at the table level (i.e., by a division ID field in the location table), then you don't need division on your form at all, because location implies division. If you want to display division on your form, for information purposes, that's easily done; but you aren't there yet, so don't worry about it now.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: joining fields (97)

    I would create two lookup tables. tblLocations holds a list of locations (LocationName) and what division they are in (DivisionName); tblDivisions holds a list of divisions (DivisionName). Use Relationships to establish a one-to-many relationship between tblDivisions and tblLocations via DivisionName.

    Next, add a combo-box (cboLocation) to your form that lists the LocationNames from tblLocations. Also include a hidden column (width=0) with DivisionName.

    Create a field on the form (txtDivision) to hold the DivisionName associated with the chosen LocationName. Make its ControlSource = cboLocation.Column(1) (the second, hidden, column of the combo-box). Also, set Locked=Yes and TabStop=No to prevent the field from being modified.

    I wouldn't bother storing both the LocationName and the DivisionName on the form's RecordSource because they are redundant (unless for some reason you don't want to use a query to join the table to tblDivisions whenever you need to get the DivisionName).

    Hope this helps.

  6. #6
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: joining fields (97)

    Thanks for just spelling it out for me. I created the tables you suggested and the relationships, then put the combo boxes on my form and everything worked perfectly! Thank you so much.

Posting Permissions

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