Could use a bit of advice...

[jdrighter]

Greetings,

I was hoping someone could offer some advice on how I would design the following project:

Student Table
- ID
- Name
- Unit (each student belongs to one specific unit)
- License type (each student could have multiple license types)

Unit Table
- Unit Name (string)

License Table
- License Type (string)

I have created a report that dynamically updates information according to what unit the student belongs to via a drop down box, i.e. while the report is open, select a unit from a drop down, press a button to apply the filter and the report automatically updates. I want to add the same kind of functionallity to the report based off of licenses as well. My original design had all license types in the Student Table as a yes/no option. I couldn't get the filter to work properly so I moved license types to its own table (which makes more sense anyways...) But, unlike the Units Table, any One student is allowed to have many licenses so this creates a bit of a problem. If anybody has some insight on this I would much appreciate it. If you're not following, please let me know and I will try to be more specific. Thanks.

j

 

[Pat Hartman]

Your table structure should be:

tblStudent
StudentID (primary key - autonumber or value generated in another app)
FirstName
MiddleName
LastName
UnitID (foreign key to tblUnit)
Other student fields

tblUnit
UnitID (autonumber, primary key)
UnitName

tblLicense
LicenseID (autonumber, primary key)
LicenseName

tblStudentLicense
StudentID (primary key fld1, foreign key to tblStudent)
LicenseID (primary key fld2, foreign key to tblLicense)
IssueDate
ExpireDate

You should use the autonumber ID's as the foreign keys. That way you can change the text values without having to propagate the changes to the child tables. DO NOT use words like Name, Date, Time, etc as column names. They are property or function names and will cause problems in VBA. Also do not use embedded spaces or special characters in your column/table names. ID is a poor choice for a column name since every table needs some type of ID field. Be more descriptive. Notice that my example uses three ID fields. Each with a unique name. That will allow you to use the primary key name as the foreign key name. You wouldn't be able to do that if you named all primary keys ID.

 

[jdrighter]

I'm pretty sure I followed the table setup. Thank you for the time, it is much appreciated!

j

 

[jdrighter]

I have set up the following tables:

tblLicense(LicenseID, LicenseName)
tblStudent(StudentID, FirstName, LastName, UnitID)
tblUnit(UnitID, UnitName)
tblStudentLicense(StudentID, LicenseID, IssueDate, ExpirationDate)

For the tblStudentLicense Table, I am using the LookUp wizard for StudentID and LicenseID. Is that correct? The end effect I am going for is this: When I add a student to the database, I want to be able to add them to a specific Unit AND give them a License (even if that License is "No License") I am also making a user friendly form to update the database as the people that will be using it now (and in the future) are not going to be too computer savvy. Also, I will add functionality to make reports on the fly querying off of UnitName and LicenseName. With the database set up as it currently is, when I add fields to a form and view the form no fields show up. I took a database course in college about 4 years ago so I'm not completely lost but we didn't use Access so I'm not to up to speed on all the little quirks and pitfalls. Any more advice would be greatly appreciated. Thanks.

j

 

[Pat Hartman]

Download my many-to-many example db and study it. It will help you understand how the "relation" table gets populated. tblStudentLicense is the relation table since it relates the data in the student table to data in the license data. Many-to-Many