Prev: Need help with table set-up and Thanks!
Next: Upgrading an .mdw to 2003 format?
From: Lkay107 on 27 May 2010 17:30
Hey Y'all,

I am creating an employee training database where I can run reports and
queries about who has taken what type of course.

What kind of relationship should be created between the tables? Based on the
some of the discussions I have seen here, I have created three tables:

1: tblemployee (with employee id set as primary key)
2: tbltraining (with trainingid set as primary key).This table has no
employee information
3: tlbcompletedtraining (in this table I have employee ids with the id's of
the training courses that they have completed)

I am unsure of what kind what kind of relationships to create between the
three tables.

Any help out there would be greatly appreciated....
From: PieterLinden via AccessMonster.com on 27 May 2010 19:06
Lkay107 wrote:
>Hey Y'all,
>
>I am creating an employee training database where I can run reports and
>queries about who has taken what type of course.
>
>What kind of relationship should be created between the tables? Based on the
>some of the discussions I have seen here, I have created three tables:
>
>1: tblemployee (with employee id set as primary key)
>2: tbltraining (with trainingid set as primary key).This table has no
>employee information
>3: tlbcompletedtraining (in this table I have employee ids with the id's of
>the training courses that they have completed)
>
>I am unsure of what kind what kind of relationships to create between the
>three tables.
>
>Any help out there would be greatly appreciated....

Employee---(1,M)---SessionRoster---(M,1)---Session---(M,1)----TrainingCourse

SessionRoster has "Grade" or "Completed" or something... to show that the
"student" finished the course.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201005/1

From: KARL DEWEY on 27 May 2010 19:23
You might find this helpful --
How do you have your tables setup? Maybe like this --
Employee --
EmpID - Autonumber - primary key
LName - text
FName - text
MI - text
HireDate - DateTime
Depart - DateTime
....etc.

Training --
CourseID - Autonumber - primary key
Title - text
Period - text (m- Monthly, q- Quarterly, yyyy- Yearly)
ReCur - Number - long integer
ReqdBy - text (OSHA 1910, HR Manual 5.8.3, Finance 4.23.1)
Grace - Number - long integer – number of days grace period allowed

EmpTraining --
EmpID - number - long integer
CourseID - number - long integer
TngDue - DateTime
CompDate - DateTime
Pass - Yes/No

Employee.Depart Is Null AND EmpTraining.Max(CompDate) AND EmpTraining.Pass =
-1

Next_Training_Required: DateAdd([Period], [ReCur], IIF(Abs(DateDiff(“d”,
Max(CompDate), Max(TngDue))) <= Grace, Max(TngDue), Max(CompDate)))

You could append training due records following update of any due record
using the two fields TngDue and CompDate. Then run query with duedate
descending to show all next training.
------------------------
Here are two post of mine on maintenace that might apply to training --
You need a ServiceReq table listing the services and interval. Then a
VehicleSvcReq the has vehicle ID, ServiceReq, Method. Method is whether
next service is the last schedule plus interval or last completion plus
interval.
The interval needs to be the lowest common denominator of all services such
as weeks if any one of the services is to be accomplished on a weekly basis -
bi-weekly - monthly - quarterly. All intervals will be multiples of the
selected interval. If fluid checks are weekly and oil change every three
months then oil change would be interval 13 - 13 weeks.
----------------------- ---------------
In a Task table have a field indicating interval number for the maintenance.
Use the lowest common denominator such as weeks, months or quarters. If you
can not make it work with the lowest common denominator then use two fields,
one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

--
Build a little, test a little.


"Lkay107" wrote:

> Hey Y'all,
>
> I am creating an employee training database where I can run reports and
> queries about who has taken what type of course.
>
> What kind of relationship should be created between the tables? Based on the
> some of the discussions I have seen here, I have created three tables:
>
> 1: tblemployee (with employee id set as primary key)
> 2: tbltraining (with trainingid set as primary key).This table has no
> employee information
> 3: tlbcompletedtraining (in this table I have employee ids with the id's of
> the training courses that they have completed)
>
> I am unsure of what kind what kind of relationships to create between the
> three tables.
>
> Any help out there would be greatly appreciated....
 | 
Pages: 1
Prev: Need help with table set-up and Thanks!
Next: Upgrading an .mdw to 2003 format?