Solved

Option Group on Form Best Practice

Posted on 2014-02-13
3
321 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:fargus47
3 Comments
 
LVL 22

Expert Comment

by:plusone3055
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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:fargus47
Comment Utility
Thank you, I appreciate the input!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now