Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Checkbox selections in report (XP)

    My client runs a crafts business--it's a brand-new enterprise for her, and I'm building a database to track her materials, sales, and customers. At her next crafts show, she plans to ask customers to fill out a paper questionnaire that asks them to check off the categories of jewelry items they're interested in (necklaces, bracelets, earrings and so on). We'll then enter that data into the DB via an Access form. The form will have check boxes for each "interest" so all we have to do is check those "interests" for each customer. Then we'll display the data in a mailing list (report).

    I've no trouble getting the report to display the usual stuff: name, address, etc. But suppose a customer is interested in three categories of jewelry (bracelets, earrings, and necklaces). How do I get multiple interests to display in a report? I hope that's a clear enough description of the problem. Thanks!

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

    Re: Checkbox selections in report (XP)

    How you structure the report depends on how you tables are set up. I wouldn't get too focused on checkboxes per se. If you have a normalized table for interests, you can have one record for each interest the person checks off and that table would be related to a customers table on the primary key of customers table. Then in a report, you would simply show all the interests for each person by grouping by person and showing the interest records in the detail.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Checkbox selections in report (XP)

    Hello, Charlotte, and thank you for the reply. I understand what you say in principle, but I always seem to get hung up as I try to make these things work. The two critical tables in this situation are the Customers and Interests. The first table has what you would expect (an AutoNumber field as key, then name, address, phone, email...and that problematic field, "Interests." I made the Interests field a lookup to the Interests table. The Interests table has just one field and eight records: Necklaces, Bracelets, Earrings, Pins, Dolls, Jars, Eggs, and Miscellaneous.

    I set up a relationship between the two tables, and then I put some dummy data into the Customers table (three records). I ended up with each customer related to one interest--not what I had in mind! I've nixed the relationship and deleted the Interests field from the Customers table. That's where things are right now. Could I trouble you to walk me through the steps to get what I need here. And then

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

    Re: Checkbox selections in report (XP)

    You need a third table, a CustomerInterest table. What you want is a many-to-many join between customers and interests, and you do that by creating two sets of one-to-may joins. The middle table would have a single record for each customer interest. So customer 1, who is interested in 3 different things, would have 3 records in the CustomerInterest table, one for each interest. The table would contain the CustomerID from the customer table and the InterestID from the interests table, and it could contain other information like the date the record was created, or the source of the record (i.e., questionnaire, phone call, etc.). Does that explain it?

    I would also recomment changing the Interests table to contain two fields, an InterestID and the actual interest. Then use the InterestID to make your joins and to be the linking field in the middle table. Otherwise, you can run into problems later if you decide to change the "interest" to something more specific or break them down further. Using a numeric key instead allows more flexibility and is faster and since good design hides the key from ordinary view, it won't make any different to selecting the items in the dropdown.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Checkbox selections in report (XP)

    A-ha! Yes! The famous linking table! I was starting to think in that direction, but you've made it clear. Thanks, Charlotte!

    Once I get the tables worked out, I'll monkey with the form and report things. If I hit any more snags, I might be back....

Posting Permissions

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