Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Option Group on Form Best Practice

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
Skip Sleeper
Asked:
Skip Sleeper
1 Solution
 
plusone3055Commented:
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
 
PatHartmanCommented:
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
 
Skip SleeperAuthor Commented:
Thank you, I appreciate the input!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now