none
Categories in a Combo Box - better in a separate table or in the same table with Row Source values? (Schema/Design - New User) RRS feed

  • Question

  • I'm at the "don't know what I don't know" stage of learning Access, so I really appreciate your input. I see two ways to create a Combo Box with the values I need. I know it's not "six of one, half-dozen of the other" but I'm struggling to work out what's better design.

    Hope you won't mind if I start by explaining the...

    Need: Some contacts are students, some are partners, some should get X newsletter, others should get Y newsletter, and the rest nothing. All of this needs to be stored, and it needs a user friendly front end for data entry. The data will be exported for mail merge (primarily in Outlook), and to analyze statistics of our customer base.

    Plan: Store every contact within the same table in the same database, then categorize. I have a category for the newsletter, a category for their status as a customer, and a Yes/No checkbox for "Do Not Contact" entries.

    My Question (or Now What?): What's the best way to accomplish all of this? I see two solutions. 

    SQL in "Row Source" calling from a separate table. In the Contact table, set the Data Type as Number, and create a one-to-many relationship with referential integrity from the Primary Key [CategoryID] to the CategoryDesc field in the Contact table, and hide the first column so the Combo Box displays text values:

    SELECT [Categories].[CategoryID], [Categories].[Category] FROM [Categories] ORDER BY [Category];

    or just set the values within the field's Row Source and keep it simple:

    "Students";"Former Students";"InfoRequests"

    Thanks for reading. I see that new users can sometimes make design mistakes that cause headaches down the road, so please let me know if there's a much better way to do this than I currently see.

    Friday, June 10, 2016 8:20 AM

Answers

  • In terms of using a value list versus table for your combo rowsource, I'm partial to the table approach for greater flexibility, ease of maintenance and the ability to quickly use the same combo/data on search forms, etc elsewhere in the database (the list is stored/maintained in one place).

    In terms of design, consider whether your 'categories' can have one or multiple values per customer.

    For example - Status.  Can a customer have more than one status, or is it a one customer/one status relationship?  If it is a one to one relationship, make a field for status right in the customer table. 

    The Do Not Contact box also belongs in the customer table (one yes/no value per customer).

    You mention newsletter subscriptions, with different newsletter options, including 'none', and I assume the possibility of more than one newsletter per customer.  Since this is a one-to-many relationship, I'd suggest a separate subscriptions table - with fields as follows:

    SubscriptionID -- autonumber/primary key

    NewsletterID -- Foreign Key, from a separate Newsletter Table (see below)

    EmployeID -- Foreign Key from the employees table

    A table for newsletter information:

    NewsletterID  -- AutoNumber/primary key

    NewsletterTitle -- Text

    (and any other fields needed to describe the newsletter - such as description, frequency, etc)

    For an easy to use interface involving one-to-many relationships, see this article:

    Create a form that contains a subform

    The article describes employees and orders, but the 'employees/newsletter subscription' concept is the same.

    -- Just my own initial thoughts.


    Miriam Bizup Access MVP

    • Marked as answer by axCZ-420 Monday, June 13, 2016 7:58 AM
    Friday, June 10, 2016 9:25 AM

All replies

  • In terms of using a value list versus table for your combo rowsource, I'm partial to the table approach for greater flexibility, ease of maintenance and the ability to quickly use the same combo/data on search forms, etc elsewhere in the database (the list is stored/maintained in one place).

    In terms of design, consider whether your 'categories' can have one or multiple values per customer.

    For example - Status.  Can a customer have more than one status, or is it a one customer/one status relationship?  If it is a one to one relationship, make a field for status right in the customer table. 

    The Do Not Contact box also belongs in the customer table (one yes/no value per customer).

    You mention newsletter subscriptions, with different newsletter options, including 'none', and I assume the possibility of more than one newsletter per customer.  Since this is a one-to-many relationship, I'd suggest a separate subscriptions table - with fields as follows:

    SubscriptionID -- autonumber/primary key

    NewsletterID -- Foreign Key, from a separate Newsletter Table (see below)

    EmployeID -- Foreign Key from the employees table

    A table for newsletter information:

    NewsletterID  -- AutoNumber/primary key

    NewsletterTitle -- Text

    (and any other fields needed to describe the newsletter - such as description, frequency, etc)

    For an easy to use interface involving one-to-many relationships, see this article:

    Create a form that contains a subform

    The article describes employees and orders, but the 'employees/newsletter subscription' concept is the same.

    -- Just my own initial thoughts.


    Miriam Bizup Access MVP

    • Marked as answer by axCZ-420 Monday, June 13, 2016 7:58 AM
    Friday, June 10, 2016 9:25 AM
  • Also, if you haven't done so already you might want to search database templates available online.  You might find one that closely matches what you are trying to do.  Using, studying and modifying an existing database to suit your needs can be a great way to learn basic design principles.

    Miriam Bizup Access MVP

    Friday, June 10, 2016 9:49 AM
  • Many great suggestions - thanks for your answer! Great idea to create a separate Subscriptions table and helpful explanation of what to include.

    Regarding 'Status' that seems correct, that it's a one-to-one relationship. So, it's better to create a Combo Box with the Row Source values within the Customers table? (Or keep Status in a separate table with its own Primary Key, and use the SQL values I mentioned?)

    Monday, June 13, 2016 8:08 AM
  • Hi -

    I'd have a field in the contacts table for status.  Status would be entered on the main contacts form through a combo box, whose rowsource would draw the options from a separate status lookup table -

    SELECT Status FROM YourStatusLookupTable ORDER BY Status

    The status lookup table would just need one field, Status with values such as Customer, Vendor, (or whatever).

    With that setup, a populated record in your  contact table might look like this:

    CustomerID: 123

    LastName: Doe

    FirstName: John

    Title:  Mr

    Status: Customer

    DoNotContact: False

    etc...

    ... and your subscriptions table may have several records for customerID 123 (John Doe), one record for each newsletter that person has subscribed to.  (If there are no subscriptions, there would be no records for Customer ID 123 in the subscriptions table.)


    Miriam Bizup Access MVP

    Monday, June 13, 2016 9:05 AM