A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table Design - Spreadsheet-like data



 
 
Thread Tools Display Modes
  #1  
Old December 4th, 2009, 11:55 PM posted to microsoft.public.access.tablesdbdesign
Cheryl
external usenet poster
 
Posts: 364
Default 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!
  #2  
Old December 5th, 2009, 12:48 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Table Design - Spreadsheet-like data

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!



  #3  
Old December 7th, 2009, 05:38 PM posted to microsoft.public.access.tablesdbdesign
Cheryl
external usenet poster
 
Posts: 364
Default 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  
Old December 7th, 2009, 07:12 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old December 7th, 2009, 08:39 PM posted to microsoft.public.access.tablesdbdesign
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.