My God I'm Screwed, Helllllpppp!

S

Sky Warren

Hello to all,

My boss wants me to design a database of ennormous magnitude. Although the
company has less than thirty employees, they are required to complete certain
training modules of which I have counted over three hundred that span across
seven departments.

One consolation is that each department "Manufacturing, QC, QA, Material
Management, Molecular Biology, Immunology and Development" has it's own
unique training modules. Examples follow:

Manufacturing modules are designated as:
MF-001
MF-002


Development modules designated as:
DV-001
DV-002

and so forth which is pretty straight forward. It gets crazy for me when
several employees require training modules from different departments. This
is where my dilemna begins. How on earth do I create a form to accomodate the
factors outlined above? I don't know where to begin.

I'm thinking of creating tables for each department with modules specific to
that department, then create a table with just the employees. Then pull the
data from those tables into the form.

Does anyone have any ideas on how to approach this? If not, can anyone
provide a template that can be modified to support a project like this one?

Maybe I better get started on my "Will Work For Food" sign! :-(

-Sky
 
S

Sprinks

Hi, Sky Warren.

Whenever I get started, I start by thinking about my Things, or Entities,
and their attributes, and their relationships to other Things. When I come
across a many-to-many relationship, I know I can represent that in an
intermediate table which is one-to-many with each of the others. I see the
following basic Things & Attributes from what you've described:

Departments
---------------
DeptID AutoNumber (PK)
Department Text

Employees
---------------
EmpID AutoNumber (PK)
LName Text
FName Text
DeptID Number (Foreign Key to Departments--use combo box)
BirthDate Date
....etc.

Courses
--------------
CourseID AutoNumber (PK)
Course Text

Since a many-to-many relationship exists between Employees and Courses (many
employees can take the same course, and an employee may take many courses),
you need an intermediate table to represent which employee has taken or needs
to take which courses. Does each employee of a particular department have to
take the same courses? If so, after loading the tables described above, you
could create a DeptCourses table that could insert EmployeeCourse records
based on the department.

EmployeeCourse
--------------------
EmpCourseID AutoNumber (PK)
EmpID Number (FK to Employees)
CourseID Number (FK to Courses)
Completed Yes/No
CompletionDate Date
Score Number
....etc.

DeptCourses
---------------
DeptCourseID AutoNumber (PK)
DeptID Number (FK to Departments)
CourseID Number (FK to Courses)

Then you could have a data entry form based on Employees, with a continuous
subform based on EmployeeCourses. When an employee completed the course, you
could check it off, and update the CompletionDate.

Hope this makes sense to you. Feel free to come back for clarification.

Sprinks
 
A

Allen Browne

Something like this:

Department table: (one record for each department)
DepartmentID primary key
DepartmentName Text
...

Unit table: (one record for each unit that can be studied)
UnitID primary key
UnitName Text
DepartmentID foreign key to Department.DepartmentID

Course table: (one record for each course)
CourseID primary key
CourseName
...

CourseUnit table: (One record for each unit in a course)
CourseID foreign key to CourseCourseID
UnitID foreign key to Unit.UnitID

Employee table: (one record for each person)
EmployeeID primary key
Surname Text
...

UnitInstance table: (one record for each time a unit is offered)
UnitInstanceID primary key
UnitID foreign key to Unit.UnitId
StartDate when this unit will begin
...

Enrol table: (one record for each person enroled in a UnitInstance)
UnitInstanceID foreign key to UnitInstance.UnitInstanceID
EmployeeID foreign key to Employee.EmployeeID
CompletedDate date this employee completed this unit (blank until
done).
ExpireDate date this employee needs to do this unit again.

EmployeeUnitRequirement table (One record for each unit needed by an
employee)
EmployeeID foreign key to Employee.EmployeeID
UnitID foreign key to Unit.UnitId

The last table gives you all units that each person needs to have completed.
 
T

TimT

Your not as screwed as you think you are...... its much worse :eek:)~
Seriously, sounds like you have certain common denominators and certain
specifics for each department.
Create your tables or table if your not going with a full relational
database and a specific form for each department.
Use either a switchboard or tab form to direct a user to the proper form.
Easy right??
Dive in and learn, that's how we all do it.
 
S

Sky Warren

Guys,

You all have good strategies for my situation. I really appreciate your
suggestions, methods, and comments. Now, I have to decide how to use what
you all have contributed. I think you guys may have saved my job, for now at
least ;-)

I'll give it a try and see how things turn out. If I don't succeed with this
though, just want all of you to know how deeply grateful I am that you tried
to help me.

Thank all of you from the heart dudes!!!

-Sky
 
S

Sky Warren

Sprinks,

I created the five tables "Departments, Employees, Courses, EmployeeCourse,
DepartCourses" with the field names suggested below. In my form I try to
pull data in from my tables but all I see is "#Name?" where data should be.

For example, here's the control source property for the Employees fields:

Textbox Control Source property
--------------------------------------------
LastName =[Employees]![LName]
FirstName =[Employees]![FName]
Dept =[Employees]![DeptID]

Of course, I don't have a Record Source specified for the form because my
tables are split up.

Do I need to combine all the tables into one? Is that what you mean by
"Intermediate" table?

-Sky
 
M

Mike Painter

Sky said:
Sprinks,

I created the five tables "Departments, Employees, Courses,
EmployeeCourse, DepartCourses" with the field names suggested below.
In my form I try to pull data in from my tables but all I see is
"#Name?" where data should be.

For example, here's the control source property for the Employees
fields:

Textbox Control Source property
--------------------------------------------
LastName =[Employees]![LName]
FirstName =[Employees]![FName]
Dept =[Employees]![DeptID]

Of course, I don't have a Record Source specified for the form
because my tables are split up.

Do I need to combine all the tables into one? Is that what you mean by
"Intermediate" table?
Click to expand...

Without a recordsource =[Employees]![LName] is meaningless to Access.

I suggest you read a little in help about tables, relationship, and forms
and maybe look at the Northwind database.

Let the wizard build a form for you and look at that.
s
I would guess that you may have worked in other databases in the past.
 
S

Sky Warren

Guys,

OK, I tried creating the database according to the model suggested by
Sprinks. The more I read the Help section, the more confused I get trying to
pull data in from all the tables that are split up. I'm accustomed to working
with just one table so here's the deal.

I'll pay $100.00 to whomever can provide me a working prototype based on
Sprinks model. I don't have enough time to read through and digest the Access
Help section. I'll have to do that later when I'm not stressed out.

Do I have any willing participants out there?

-Sky
 
T

Tom Wickerath

Sky,

Uh-oh. Here we go again! I know of one Access MVP who might be interested
for $100, since this person responded to a similar offer:

http://groups.google.co.uk/groups?hl=en&lr=&th=9946f0a44313452f&rnum=1


Tom
____________________________________

:

Guys,

OK, I tried creating the database according to the model suggested by
Sprinks. The more I read the Help section, the more confused I get trying to
pull data in from all the tables that are split up. I'm accustomed to working
with just one table so here's the deal.

I'll pay $100.00 to whomever can provide me a working prototype based on
Sprinks model. I don't have enough time to read through and digest the Access
Help section. I'll have to do that later when I'm not stressed out.

Do I have any willing participants out there?

-Sky
 
S

Sky Warren

Tom,

Based on the replies that poor guy got from the programmers reading his
post, I think I'll pass and save myself the humiliation that's sure to come
if I make the offer he made. Since I normally do this database stuff as a
hobby and just to learn it, if I was accomplished enough I would have taken
the offer. And since I don't have a few thousand dollars lying around, my
only choice is swim or sink.

But, since I have a good sense of humor I may just post in the forum anyway
to give the guys and girls a few more good laughs :) I gotta admit I had
some chuckles too reading the programmer replies. I imagine if your really
good at Access development, then a $100.00 offer really is an insult. On the
flip side, if your really poor and desperate (like me) then $100.00 is
probably half your life savings :-(

Lesson to be learned here is: Get a good paying job if you can!!!

Thanks Tom for leads though...

-Sky
 
T

Tom Wickerath

Sky,

I know a bit more of the inside information regarding Scott's database,
since I volunteered, by private e-mail, to run an analysis of his database
using my copy of Total Access Analyzer. I was really doubtful as to the
quality of an application that could be purchased for $100. I expected it to
be full of errors.

I was quite surprised when I saw the familiar name of an Access MVP clearly
indicated as the author of this database. In this case, Scott "that poor guy"
turned out to work for a large company, so he certainly should have had the
resources available. Instead, he was probably patting himself on the back for
how cheaply he got the database developed.

I'm glad you have a good sense of humor. I encourage you to continue posting
questions, as they arise, to this newsgroup.

Good luck on your project.

Tom

PS. It turns out that the price apparently ended up being $250, according to
a message that Scott mistakenly sent to me, which I'm sure was meant for the
person who did the work. This amount appeared to be in dispute, judging by
the tone of the message, which included the statement:

"Sorry Jeff, just looked at the invoice for the first time. I only told her
to send $250. I hope that's enough. We only pay for work not phone calls,
etc. We can communicate online for free using AIM."

The last two sentences speak volumes.

______________________________________

:

Tom,

Based on the replies that poor guy got from the programmers reading his
post, I think I'll pass and save myself the humiliation that's sure to come
if I make the offer he made. Since I normally do this database stuff as a
hobby and just to learn it, if I was accomplished enough I would have taken
the offer. And since I don't have a few thousand dollars lying around, my
only choice is swim or sink.

But, since I have a good sense of humor I may just post in the forum anyway
to give the guys and girls a few more good laughs :) I gotta admit I had
some chuckles too reading the programmer replies. I imagine if your really
good at Access development, then a $100.00 offer really is an insult. On the
flip side, if your really poor and desperate (like me) then $100.00 is
probably half your life savings :-(

Lesson to be learned here is: Get a good paying job if you can!!!

Thanks Tom for leads though...

-Sky
 
T

Tom Wickerath

Sky,

I have not received a message from you.


Tom
______________________________________

:

Tom,

I wrote you an email. Read it and let me know what you think.

-Sky
 
S

Sky Warren

Hi Again,

Tried writing to the email address listed here in the forum, but the email
keeps coming back as undeliverable. I noticed a different one for you at
QBuilt. Should I contact you there?

-Sky
 
T

Tom Wickerath

Sky,

I'm not sure what address you tried to use. This one should work:
ZXaos 168 AT @ Zcomcast . Znet (<---Remove all capitolized letters and
spaces).

You can use the QBuilt address as well.

Tom
______________________________________

:

Hi Again,

Tried writing to the email address listed here in the forum, but the email
keeps coming back as undeliverable. I noticed a different one for you at
QBuilt. Should I contact you there?

-Sky
______________________________________

:

Sky,

I have not received a message from you.


Tom
______________________________________

:

Tom,

I wrote you an email. Read it and let me know what you think.

-Sky
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems Loading Large String Array into Array variable 6
New books 10

Top