Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple selections (2003)

    Hi.

    I am a complete Access newbie, although I am very familiar with Excel which may be the root of my problem in that I want to just enter the data into the table without having to go to the trouble of designing a form just for me to use!

    Here's the problem:

    I have a school travel questionnaire which I need to transcribe from the paper returns into a database so that I can analyse the data. On this questionnaire I have several questions where the answer can be multiple options. For example, to take a simple case, "how do you get to school" - the tick boxes are Car, Bike, Walk, Bus, Taxi. Several people use a mix and tick e.g. both Car and Walk.

    - Option 1 was to create a dead simple and entirely horrible flat table that included each option as a separate field with YES/NO check boxes. One questionnaire, one record. Horrible table, not what a database should be like, but it worked.

    - Option 2 set up a table called "Mode" where the modes were fields. Each record was then a mix of different modes, so that I had all bases covered. This also worked, and I could run queries on it to see how many people walked (even if they also sometimes took the car). However, I needed to define a unique name for each set of options (albeit this could be an autonumber), and some of the questions had 8 entries which would have meant 256 entries to scroll through to cover each possible set of combinations.

    - Option 3, which I have so far failed to achieve, ought to be much better than any of these, but I simply don't know where to start. I would like to be able to define a table called "Mode" and put the modes into it as individual records, and then pick them off a list in a "multiple selection list box" type of operation in the one "Answers" table where I enter the questionnaire responses.

    I'm sure that I'm not the only one doing this, but quick searches here haven't turned up what I was hoping (unless it's somewhere I haven't looked yet). Can anyone help me, or point me at a resource if one already exists.

    Many thanks.

    Stuart

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

    Re: Multiple selections (2003)

    You have a many-to-many relationship between persons and modes of transport: one person can use several modes of transport, and several persons can use the same mode of transport. The "official" way of handling this is by creating an intermediate table in which each person - mode of transport combination is a separate record, containing the person ID field and transport ID field.

    But this setup means that you should use a main form and subform to enter data. See <post#=364,203>post 364,203</post#> for a sample database demonstrating how to use such an intermediate table in a main form - subform setup.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selections (2003)

    Thanks - I shall go and investigate

    Stuart

Posting Permissions

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