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
Solved

Option Group on Form Best Practice

Posted on 2014-02-13
3
331 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
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 35

Accepted Solution

by:
PatHartman earned 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

809 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