Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    check boxes (Access 2000)

    I am making a database of people and i wanted to include check boxes for hobbies, if they are interested in the hobbie you i will check it, if not i wont

    the only problem is i want to be able to create a form that i can select any hobby and find out whos interested in these, since they are different columns, how could i do this?

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

    Re: check boxes (Access 2000)

    Before starting to design a from, you must think about the data structure you are going to use. The most flexible structure is one with three tables:

    1. A "Persons" table that contains names, adresses, birth dates etc. It has a unique key PersonID (for example an AutoNumber field.)
    2. A "Hobbies" table that lists the available hobbies. It has a unique key HobbyID (for example an AutoNumber field.)
    3. An intermediate "PersonsHobbies" table that contains one record for each hobby a person has. So if I have hobbies reading and bicycling, there will be two records for me; if you have hobbies baseball, jogging and woodworking, there will be three records for you. This table has a composite key consisting of PersonID and HobbyID. The table can contain other information that is specific for the person-hobby combination (for example since when the person has been active in this hobby).

    The way to present this structure is typically in a main form (persons) and continuous subform (hobbies of the person).

    The advantage of this structure is that it is easy to add hobbies; there is no problem having a long list of hobbies. Tallying is very easy.

    A simpler but less flexible structure would be to have a Yes/No field in the Persons table for each hobby. The corresponding form is easy to design, but each time you think of a new hobby, you have to add a field to the table and to change the design of the form. Tallying is harder.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check boxes (Access 2000)

    I understand # 1 and # 2, but not # 3. I dont understand how the third table would know what hobbies each person liked?

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

    Re: check boxes (Access 2000)

    The table doesn't know anything, it is dumb. You populate it using a form and subform. I have attached an extremely simple database illustrating this. It contains just the three tables I mentioned earlier in this thread, and a main form and subform. Look at the relationships between the tables, at the way the subform is linked to the main form through the Link Master Fields and Link Child fields properties, and at the way the user can select hobbies from a combo box.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check boxes (Access 2000)

    Is there any way from this I can say find all people with this hobby in a standard form, and be able to select hobbies from a drop down and see who enjoys each hobby?

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

    Re: check boxes (Access 2000)

    My demo contains a main form showing persons and a subform showing their hobbies. It shouldn't be too hard to create the reverse: a main form that shows hobbies and a subform that shows the persons with that hobby. The Combo Box Wizard can create a search combo box for you.

Posting Permissions

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