I’m building a basic expense tracking application and would like it to include a list of 30+ general accounts (categories) in one List Box (which I know how to do). And a second List Box that changes its list of more granular (sub-category) options based on the selection made using the general accounts List Box.
I believe the second (subordinate) List Box options could be defined in a second table with multiple fields (columns), with each field representing a different option of the general accounts listed in the first List Box. I also believe through research this could be done with an appropriate SQL statement, but don’t have a clue how to construct it. So my questions are:
a) Is this the right approach to the design of the subordinate list box?
b) What would the SQL statement format look like?
c) How would I need to construct the control(s) of the List Boxes to accommodate the above?
Thanks, Erik
A quick question about categories and sub-categories. Will one sub-category be in multiple categories, or just one category for each sub-category?
No. Each sub-category is unique and is associated with only one category.
I quickly put this simple odb together with a form demonstrating the one-to-many relationship which you requested and also the many-to-many relationship if you want to look at it. The one-to-many utilizes two tables (“categories”,“subcategories”) while the many-to-many utilizes a third table (“joins_Cats_Subs”) and also a query.
MultiCategoriesDemo.odb (14.4 KB)