Hi,
Simple example here to apply proper structure to my much more complex database.
Two tables, Students and Classes.
Classes table has two fields - Class_ID (primary key), Class_Name
Students Table has three fields - Student_ID, Student_Name, Class_ID
I'm setting up a query based on the Students table and add both tables and establish he relationship from Class_ID in the Students table to Class_ID in the Classes table (one-to-many).
My question is which fields should I include in the query - Students.Class_ID and/or Classes.Class_ID and/or Classes.Name in order to do the following for forms and reports with the query as the record source.
- Display Class_Name from Classes table (easy)
- Sort records in form based on Class_Name
- Add new records to the Students table from a form
- In VBA code from a form button event, add multiple Student records using the Class_ID from the current record in focus
OR should I just be storing the class name itself in the Students record once I pick the class from the Classes table on the form using a combo box?
Again, this is a very simple example. My db has a table that needs to select and store data from from many other tables. I'm wondering if I should be keeping the reference ID from those tables or store the actual data. Storing the actual data causes duplication of data (a waste) and causes grief if field values need to change, but how much grief am I causing myself by referencing ID's to display field names and creating new records? Any advice would be appreciated. Thanks so much!