###################################################################################################
The first method makes use of a combo box. It stores data from the input form "Collection_Combo" to the equaly named table. Query "qDistinct_Combo" feeds the combo box with distinct item names that have been used so far in the "Collection_Combo" list. This is an informal appropach that does not require any database relation.
###################################################################################################
The second method makes use of a list box. It stores data from the input form "Collection_Listbox" to the equaly named table. Query "qListboxItems" feeds the list box with names and item IDs from the related table "ListboxItems". The list box itself is fed by query "qListboxItems" (names and IDs of items). The list box shows item names but enters the corresponding Item-IDs into the collection table. You can only enter items that stored in the related table "ListboxItems". The same form document includes a second form to enter new list box items into the white text box.
This is the way how you handle one-to-many relations by means of list boxes. Unlike the combo box approach you save a pointer (a reference ID) to another table which may store a lot more information about each item than just the name. In this form you enter availlable item-IDs to your collection list.
A 3rd form document is named after table "ListboxItems". It shows the 1-n relation from the other side. You enter collection entries related to the item selected in the parent form which shows the availlable items of table "ListboxItems". The parent form has a dependent child form showing the related collection entries of the item selected in the parent form.
A second child form is based on query "qAggregation" and shows the sum, average and row count for the item selected in the parent form.
The picture shows the relation between the availlable items and their related collection entries. Call menu:Tools>Relations from the database main window. The fields marked by a yellow key are primary keys (unique "row numbers"). The "Item_ID" of the left table is called "foreign key" because it points to the other tables primary key.
- One-to-many (1-n) relation between a collection list and a list of availlable items. menu:Tools>Relations
- Listbox_Relation.png (16.72 KiB) Viewed 19251 times
The primary keys (item-IDs, indicated by yellow key icons) are unique "row numbers". The database adds them automatically as you enter new records to the tables. They also improve speed when looking up related stuff in multiple tables.
There is also an additional unique index on table "ListboxItems" to guarantee that you can not enter the same item name twice into list of availlable items. The index was added via right-click table "ListboxItems" > Edit... and then menu:Tools>Index Design... These additional indices help to improve speed while they my enforce uniqueness beyond the uniqueness of primary keys. Excercise: Add a non-unique index on the item names "N" to the combo-collection table. It will speed up filtering when the collection grows.