Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Option Group on Form Best Practice

Posted on 2014-02-13
3
Medium Priority
?
348 Views
Last Modified: 2014-02-18
Hi Experts,

I am working on a form that will allow users to create menus for breakfast, lunch and supper from a list of menu items.  Each menu item is assigned a particular menu category to reduce the length of the available items and hopefully reduce user errors.  

We have four dining rooms and most of the items will be served in all four but occasionally a single item will be served in only one dining room.  I have tried to use query based combo boxes and option groups but it seems cumbersome because each menu item will require--I think--a unique box or group.  The form I'm using is called frmMenuCreator.

I'm not sure what the best way to handle this design requirement and any advice would be greatly appreciated.  As an aside, I plan on upsizing this db to SQL at some point.

Thank you!
Sample-DB.accdb
0
Comment
Question by:Skip Sleeper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 39857426
this could be handled in a number of ways... if you can tell on a consistent basis when only one item is being served in one dining room you can set the visible to true/false that day on certain items..
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39857503
You can use clunky workarounds that sound like they make sense because you have only a few exceptions but that is today.  Tomorrow you could have different/more exceptions which will require more coding and more workarounds so you might consider doing it right the first time and normalizing the schema.

This takes four or five tables.
1.  Menu items
2.  Dining rooms
3.  Meals
4.  Items served at a meal in a dining room.
5.  Menus

This allows you to choose 1 from table 1, 1 from table 2 and 1 from table 3 to produce table 4.  Table 5 is the menu for a day/week/whatever.  This grouping is only needed if you wanted to group all the menus for a period together.

If you want to make further restrictions, the schema gets more complicated.  For example, if you decide you only want to serve hamburgers at lunch or pancakes at breakfast, you would need a table that grouped Menu Items with meals.  You would then choose from that list for table four rather than choosing the two attributes separately.  In the greater scheme of things, you have the most flexibility if you don't restrict specific items to specific meals.  Some people like pancakes for supper.  But it wouldn't be wrong to do that.  It depends on how restrictive your menus are and how long the lists would be if you didn't do some pre-filtering.  There are guiding principles for normalization but it really comes down to modeling your business rules as they exist and thinking about how they might change.  For example,  if you decide right now that you will never serve pancakes for supper and go with the intermediate table that groups items with meals, this will remove later flexibility.  You will still be able to serve pancakes for supper but you would need to add them to the items valid for supper menus list.
0
 

Author Comment

by:Skip Sleeper
ID: 39868309
Thank you, I appreciate the input!
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question