April 9, 2024
Contribute to the Microsoft 365 and Office forum!
Click here to learn more 💡
May 10, 2024
Access Forum Top Contributors:
I need help with something in Microsoft Access
Report abuse
Thank you.
Reported content has been submitted
* Please try a lower page number.
* Please enter only numbers.
I suggest you review some tutorials on setting up your tables and relationships. If you don't get these correct your application will be built on sand.
A database should have at least these tables:
- Employees
- Classes
- EmployeeClasses (which students are registered in which classes)
- ClassSchedule (linking classes to scheduled offerings)
There could be several more tables such as required trainings etc.
It's best to make a good guess at your tables and post back here to get some feedback.
Minnesota
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
As Duane says, get your table design right FIRST. What you have is what's called "encoding data in table names" - having a table for every required subject is simply WRONG and will make your database a nightmare to manage!
Here are some resources that might help you get started:
Utter Access discussion forum and resources:
http://www.utteraccess.com
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page:
http://www.mvps.org/access/resources/index.html
Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/
A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html
A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Good thing you haven't put any data intro tables yet, because your tables are not well designed.
Having a separate table for each course is wrong design. It encodes data using the table, rather than a field in the table identifying the course.
let me explain that a database is a collection of tables. Each table defines a specific type of entity. So your point about not "having to do a database based on the trainings and a separate one based on the employees" doesn't make sense.
So, you appear to have 2 main entities here. Employees and Training courses. So you should, as Duane described, have a table of employees and a table of courses. An entity has attributes, these make up the fields in your tables. So attributes of the employee entity are things like first & last name, DOH, etc. Attributes of a Course would be the name, a description, ehter its required and, if so, when it was required to be completed.
The next thing you need is a record of which employee took which course and when. You have a many to many relationship between employees and courses (an employee can take multiple courses and a course can be given to multiple employees). So that's where the third table Duane mentioned comes in. That table Would contain the EmployeeID, the CourseID and an indication of when it was given.
With the above structure, you can easily do reports for an individual employee, individual course or specific requirement.
The links John gave go into much greater depth about entities, attributes, normalization etc. So I strongly suggest you stop any design until you understand better how to design.
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
A Many to Many relationship is just composed of two One to Many relationships. In your example, Duane's and Scott's suggested tables are Employees, Courses, and Enrollment.
There's a one to many relationship from Employees to Enrollment - each employee can enroll in zero, one, or multiple Courses.
There's also a one to many relationship from Courses to Enrollment - each course can enroll zero, one or multiple Employees.
When you have all three tables populated (using Forms and Subforms as described in, among many other places, Crystal's tutorials in the link I posted), you can then create a Query joining all three tables to see who is enrolled where.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
https://onedrive.live.com/redir?resid=5E6898A392385C62%21105
That is what I started. Am I on the right path?
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Yes, this is much better. A couple of points. Don't use the generic ID name. Rename it TrainingID and EmployeeID. This will make it clear what it links back to. Description is a reserved word so don't use it for an object name, use TrainingDescription instead. You can set the Caption property to Description so that is what appears when you use the wizards to create forms and reports.
I would add a multi field unique index on the combination of EmployeeID and TrainingID in CompletedTrainings so you ensure you don't get duplicates. If an employee can take a training more than once, then add the date field to that index.
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
I never use Caption properties since when viewing query datasheets the actual field name is hidden. It's a matter of taste. I also don't use Input Masks (again just my opinion).
Most of us veteran programmers use some type of naming convention when creating tables, forms, reports, queries, etc. You should find one you can live with and use it religiously.
Minnesota
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Kim,
While in table design, you can create indexes by selecting one or more fields. One of the properties of the index is to make them unique. The Order Details table in Northwind has a multifield unique index on the OrderID and ProductID fields. This eliminates the possibility of an order having the same product multiple times.
Minnesota
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Question Info
Last updated October 5, 2021 Views 102 Applies to: