If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design - Spreadsheet-like data
I am new to Access (using 2007) and partly as a learning experience, I’m
trying to build a database to handle training records for my division. So far I have four tables – although I’m not sure I really need one of them – tblEmployees, tblRoles, tblCCC, and tblCourses. tblCCC is a list of department identifiers and is the one that might go away. I will probably need another table for employee roles, since an employee can have multiple roles. And of course I will need a table to hold the actual training data. tblEmployee contains lngAutoID, strBadge (PK), strLastName, strFirstName, strCCC, ysnActive tblRoles contains lngAutoID (PK) and strRoles tblCourses contains lngAutoID (PK) and strCourseName Courses are assigned to an employee based on their role. I have an Excel spread sheet with roles as column headers (B2:K2) and class names down column A (A2:A26). An X in the intersection between role and class indicates that that class is required for that role. How do I take that data and turn it into Access object(s) that I can query, use in reports, etc. Any other thoughts on the design thus far would be greatly appreciated! Thank you! |
#3
|
|||
|
|||
Table Design - Spreadsheet-like data
Ok, so let's simplify the problem a bit. Assume we have 3 roles: Manager,
Worker, and Secretary, and 5 classes, A, B, C, D, & E. Manager is required to take clases A, B, & C, Worker is required to take classes B, C, & D, and Secretary is required to take classes D & E. So you're suggesting the tables would look like this? tblRole: 1 Manager 2 Worker 3 Secretary tblCourse 1 A 2 B 3 C 4 D 5 E tblRoleRequiredCourse 1 1 1 2 1 2 3 1 3 4 2 2 5 2 3 6 2 4 7 3 4 8 3 5 tblRoleRequiredCourse contains a lot of duplicates, both role numbers and course numbers. With the data in Excel roles and courses are only entered once with a Y/N or 1/0 at the intersection indicating that the class is required. Is there no way to take advantage of that type of format in Access? Cheryl "Steve" wrote: I suggest the following tables: TblDepartment DepartmentID Department TblEmployee EmployeeID BadgeNumber FirstName LastName DepartmentID TblRole RoleID Role TblCourse CourseID Course TblRoleRequiredCourse RoleRequiredCourseID RoleID CourseID TblEmployeeRole EmployeeRoleID EmployeeID RoleID TblEmployeeCourseCompleted EmployeeCourseCompletedID EmployeeID CourseID DateCompleted Steve "Cheryl" wrote in message ... I am new to Access (using 2007) and partly as a learning experience, I'm trying to build a database to handle training records for my division. So far I have four tables - although I'm not sure I really need one of them - tblEmployees, tblRoles, tblCCC, and tblCourses. tblCCC is a list of department identifiers and is the one that might go away. I will probably need another table for employee roles, since an employee can have multiple roles. And of course I will need a table to hold the actual training data. tblEmployee contains lngAutoID, strBadge (PK), strLastName, strFirstName, strCCC, ysnActive tblRoles contains lngAutoID (PK) and strRoles tblCourses contains lngAutoID (PK) and strCourseName Courses are assigned to an employee based on their role. I have an Excel spread sheet with roles as column headers (B2:K2) and class names down column A (A2:A26). An X in the intersection between role and class indicates that that class is required for that role. How do I take that data and turn it into Access object(s) that I can query, use in reports, etc. Any other thoughts on the design thus far would be greatly appreciated! Thank you! . |
#4
|
|||
|
|||
Table Design - Spreadsheet-like data
Given your example (although not what was suggested), you would create a
form/subform for data entry. The main form would be based on TblRole. The subform would be based on a query that included TblCourse and TblRoleRequiredCourse. You would design this query to list all the courses. Then all you would need do is check off in the subform all the courses the selected Role in the main form was required to take. Steve "Cheryl" wrote in message ... Ok, so let's simplify the problem a bit. Assume we have 3 roles: Manager, Worker, and Secretary, and 5 classes, A, B, C, D, & E. Manager is required to take clases A, B, & C, Worker is required to take classes B, C, & D, and Secretary is required to take classes D & E. So you're suggesting the tables would look like this? tblRole: 1 Manager 2 Worker 3 Secretary tblCourse 1 A 2 B 3 C 4 D 5 E tblRoleRequiredCourse 1 1 1 2 1 2 3 1 3 4 2 2 5 2 3 6 2 4 7 3 4 8 3 5 tblRoleRequiredCourse contains a lot of duplicates, both role numbers and course numbers. With the data in Excel roles and courses are only entered once with a Y/N or 1/0 at the intersection indicating that the class is required. Is there no way to take advantage of that type of format in Access? Cheryl "Steve" wrote: I suggest the following tables: TblDepartment DepartmentID Department TblEmployee EmployeeID BadgeNumber FirstName LastName DepartmentID TblRole RoleID Role TblCourse CourseID Course TblRoleRequiredCourse RoleRequiredCourseID RoleID CourseID TblEmployeeRole EmployeeRoleID EmployeeID RoleID TblEmployeeCourseCompleted EmployeeCourseCompletedID EmployeeID CourseID DateCompleted Steve "Cheryl" wrote in message ... I am new to Access (using 2007) and partly as a learning experience, I'm trying to build a database to handle training records for my division. So far I have four tables - although I'm not sure I really need one of them - tblEmployees, tblRoles, tblCCC, and tblCourses. tblCCC is a list of department identifiers and is the one that might go away. I will probably need another table for employee roles, since an employee can have multiple roles. And of course I will need a table to hold the actual training data. tblEmployee contains lngAutoID, strBadge (PK), strLastName, strFirstName, strCCC, ysnActive tblRoles contains lngAutoID (PK) and strRoles tblCourses contains lngAutoID (PK) and strCourseName Courses are assigned to an employee based on their role. I have an Excel spread sheet with roles as column headers (B2:K2) and class names down column A (A2:A26). An X in the intersection between role and class indicates that that class is required for that role. How do I take that data and turn it into Access object(s) that I can query, use in reports, etc. Any other thoughts on the design thus far would be greatly appreciated! Thank you! . |
#5
|
|||
|
|||
Table Design - Spreadsheet-like data
Steve is correct regarding the normalized design. If you add new roles or
classes, you don't want to have to change table structures, forms, reports, queries, etc. Adding new roles or classes should only involve adding records to the appropriate tables. You shouldn't allow user-interface to drive your table structures. Typically you can (with some effort) duplicate a non-normalized interface with normalized tables. -- Duane Hookom Microsoft Access MVP "Cheryl" wrote: Ok, so let's simplify the problem a bit. Assume we have 3 roles: Manager, Worker, and Secretary, and 5 classes, A, B, C, D, & E. Manager is required to take clases A, B, & C, Worker is required to take classes B, C, & D, and Secretary is required to take classes D & E. So you're suggesting the tables would look like this? tblRole: 1 Manager 2 Worker 3 Secretary tblCourse 1 A 2 B 3 C 4 D 5 E tblRoleRequiredCourse 1 1 1 2 1 2 3 1 3 4 2 2 5 2 3 6 2 4 7 3 4 8 3 5 tblRoleRequiredCourse contains a lot of duplicates, both role numbers and course numbers. With the data in Excel roles and courses are only entered once with a Y/N or 1/0 at the intersection indicating that the class is required. Is there no way to take advantage of that type of format in Access? Cheryl "Steve" wrote: I suggest the following tables: TblDepartment DepartmentID Department TblEmployee EmployeeID BadgeNumber FirstName LastName DepartmentID TblRole RoleID Role TblCourse CourseID Course TblRoleRequiredCourse RoleRequiredCourseID RoleID CourseID TblEmployeeRole EmployeeRoleID EmployeeID RoleID TblEmployeeCourseCompleted EmployeeCourseCompletedID EmployeeID CourseID DateCompleted Steve "Cheryl" wrote in message ... I am new to Access (using 2007) and partly as a learning experience, I'm trying to build a database to handle training records for my division. So far I have four tables - although I'm not sure I really need one of them - tblEmployees, tblRoles, tblCCC, and tblCourses. tblCCC is a list of department identifiers and is the one that might go away. I will probably need another table for employee roles, since an employee can have multiple roles. And of course I will need a table to hold the actual training data. tblEmployee contains lngAutoID, strBadge (PK), strLastName, strFirstName, strCCC, ysnActive tblRoles contains lngAutoID (PK) and strRoles tblCourses contains lngAutoID (PK) and strCourseName Courses are assigned to an employee based on their role. I have an Excel spread sheet with roles as column headers (B2:K2) and class names down column A (A2:A26). An X in the intersection between role and class indicates that that class is required for that role. How do I take that data and turn it into Access object(s) that I can query, use in reports, etc. Any other thoughts on the design thus far would be greatly appreciated! Thank you! . |
Thread Tools | |
Display Modes | |
|
|