Don't know what it would be called... HELP...

[Cristina]

Okay, this is what I am trying to do...

I have a group of employees that need training depending on their trade and
position.

I am trying to set up a form that shows ONLY the training needed based on
the Trade and Position that is selected in a single table.

I don't know how I would go about setting this up. Do I create a
table/query/macro or what?

Please help with step by step directions. THANK YOU!!!

 

[John W. Vinson]

Okay, this is what I am trying to do...

I have a group of employees that need training depending on their trade and
position.

I am trying to set up a form that shows ONLY the training needed based on
the Trade and Position that is selected in a single table.

I don't know how I would go about setting this up. Do I create a
table/query/macro or what?

Please help with step by step directions. THANK YOU!!!

Step by step directions for completing your complete application goes way
beyond what's reasonable for unpaid volunteer support. We'll be glad to help
point you in the right direction, but building your database for you is not
what this forum is about.

You will need several tables (you may have some, you don't say): a table of
Employees, a table of Trades, a table of Positions, and a table of
TrainingCourses. You will need various Queries to link them, and you will need
Forms to present the information to the user and allow them to edit it (e.g.
assigning employees to a particular course).

If you'll post a description of your current table structure (if any) and
ideas about what you want to add to accomplish this, we'll be glad to try to
help.

For some tutorials and other resources for designing your own database see

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

A free tutorial written by Crystal (MS Access MVP):
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

 

[Cristina]

Hi there John,

Thank you for your response. I didn't mean for somebody to build the
database for me. I do get the gist of most of it.

I already have a table set up for each of the items you list. And I do have
a few forms completed. It's the queries that I am having the most difficult
with. I tried setting up the queries but they weren't for some reason doing
what I was hoping for. I was linking the three tables into one query, but I'm
guessing because there was no common thread linking the tables it wasn't
quite right.

Perhaps I should've posted this in the queries section but I wasn't sure
that was the right area to do it. Also, I asked for step by step because once
before the replies I received were using VBA and I'm not familiar with the
VBA coding.

THANK YOU again and I hope to receive a little more help.

 

[John W. Vinson]

I already have a table set up for each of the items you list. And I do have
a few forms completed. It's the queries that I am having the most difficult
with. I tried setting up the queries but they weren't for some reason doing
what I was hoping for. I was linking the three tables into one query, but I'm
guessing because there was no common thread linking the tables it wasn't
quite right.

Perhaps I should've posted this in the queries section but I wasn't sure
that was the right area to do it. Also, I asked for step by step because once
before the replies I received were using VBA and I'm not familiar with the
VBA coding.

Thanks for the clarification, Christina, and apologies that my reply was so
snarky.

It does sound like the basic problem is in either the table relationships, or
constructing the queries. A three-table query will be useful for a Report but
you certainly would not want one for a data entry/editing form (you'ld use a
form with subforms instead). Perhaps you could post some information about
your tables - name of table, its significance to your application if that's
not obvious from the name, its primary key, and how (if at all) the tables are
related.

You may also want to describe what you're trying to accomplish with the query.

 

[Cristina]

Yeah, I don't even know where to start with this. Here is what I'm trying to
accomplish.

I work in the training department for a company, and when a new employee
comes in we fill out the basic information including theyre TRADE, POSITION,
and JOB. (i.e. trade = Avionics, position = Avionics Mechanic, Job = Delta)
For that particular employee he needs training based on his trade, position,
and job. So when we go into the database we have combo boxes listed in a
table called "Employee Information" that tells us what their trade, position,
and job is. This information is entered into a form created. I have 4 tables
with the information needed. List_of_positions, List_of_trades,
List_of_courses, & List_of_jobs (Simple & easy to understand)

SOOOOOO... with this information I want in a Form or Report (not sure which
would be best) to come up with ONLY the courses needed for the particular
trade, position, AND job.

I don't know if theres a way I can post my database so you can look at the
form I created. So you can see the format of it.

THANK YOU AGAIN GREATLY for your response.

 

[John W. Vinson]

Yeah, I don't even know where to start with this. Here is what I'm trying to
accomplish.

I work in the training department for a company, and when a new employee
comes in we fill out the basic information including theyre TRADE, POSITION,
and JOB. (i.e. trade = Avionics, position = Avionics Mechanic, Job = Delta)
For that particular employee he needs training based on his trade, position,
and job. So when we go into the database we have combo boxes listed in a
table called "Employee Information" that tells us what their trade, position,
and job is. This information is entered into a form created. I have 4 tables
with the information needed. List_of_positions, List_of_trades,
List_of_courses, & List_of_jobs (Simple & easy to understand)

Well, you certainly need a list of people as well, right!? you can't assign an
employee to a training course unless you know who the employee is and what her
trade, etc. are.

And if you have Combo Boxes in a Table, and especially if you're trying to
enter data into the Table... DON'T do either. Check the critique at
http://www.mvps.org/access/lookupfields.htm. Tables are for storing data, and
for NO OTHER PURPOSE; the user should never even *see* a table datasheet.

SOOOOOO... with this information I want in a Form or Report (not sure which
would be best) to come up with ONLY the courses needed for the particular
trade, position, AND job.

To enter or update data you will need a form (reports are display-only). This
form will have combo boxes which will probably depend on data in the Employee
table (if the employee table exists, which is still unclear to me). If you'll
post the relevant fieldnames and relationships of your tables, we'll gladly
help you do so.

You very likely will want a Report at some point to print out who's been
assigned to which course, but that is a separate issue.

I don't know if theres a way I can post my database so you can look at the
form I created. So you can see the format of it.

The format (in terms of appearance and layout) is irrelevant to this
discussion, no matter how important it is to the users. Let's get the airframe
and structure right before we start working on the paint job.

 

[Cristina]

"Well, you certainly need a list of people as well, right!? you can't assign an
employee to a training course unless you know who the employee is and what her
trade, etc. are."

I have a table/form that are connected that allows for me to enter the
employee information. I have it set up so that everything is inputted through
the Form not the table. Any other user will not being seeing the table that
holds the employee information it will all be entered through the form that
contains the combo boxes.

To enter or update data you will need a form (reports are display-only). This
form will have combo boxes which will probably depend on data in the Employee
table (if the employee table exists, which is still unclear to me). If you'll
post the relevant fieldnames and relationships of your tables, we'll gladly
help you do so.

You very likely will want a Report at some point to print out who's been
assigned to which course, but that is a separate issue.

Okay as far as tables go. Here is what I have and what the tables contain.
There is:

1. Table "Employee Information" that has fields for Empl # (# field), Name
(text field), Project (text field), trade (text, combo box), position (text,
combo box), whether they are a contractor (yes/no field), whether it is a
change of position (yes/no field), If they were on a per diem break (#
field), if they were on LOA (# field), or if they are a re-hire (# field).

2. Table "List_of_trades" that only has one field that lists each of the
trades within the company. (This is used in conjunction with the "Employee
Information" table and the combo boxes")

3. Table "List_of_positions" that only has one field that lists each of the
positions. (This is used in conjunction with the "Employee Information" table
and the combo boxes")

4. Table "List_of_Courses" that only has one field that lists each of the
courses within the company. (This is not linked to anything as of right now)

I don't have any queries or reports set up. I only have those tables listed
above and two forms.

1. Form "Employee Information" that allows for the users to enter the
necessary information for each employee.

2. Form "HR Report" is the form that I created that links the "employee
information" TABLE and is the form that I am trying to contain only the
courses that are needed per trade/position.

I hope that I was able to clarify some of the needed information.

 

[John W. Vinson]

1. Table "Employee Information" that has fields for Empl # (# field), Name
(text field), Project (text field), trade (text, combo box), position (text,
combo box), whether they are a contractor (yes/no field), whether it is a
change of position (yes/no field), If they were on a per diem break (#
field), if they were on LOA (# field), or if they are a re-hire (# field).

I'd certainly use FirstName and LastName fields rather than Name (so you can
more easily sort or search by either name); a "change of position" would not
seem to be a characteristic of a person, though!

And I again would suggest that combo boxes *SHOULD NOT EXIST* in your tables.
You'll use combo boxes on Forms, of course, but they serve no useful purpose
in tables. I presume that the Lookup Field property of these fields reference
the List_Of_Trades and List_Of_Positions tables?

2. Table "List_of_trades" that only has one field that lists each of the
trades within the company. (This is used in conjunction with the "Employee
Information" table and the combo boxes")

3. Table "List_of_positions" that only has one field that lists each of the
positions. (This is used in conjunction with the "Employee Information" table
and the combo boxes")

4. Table "List_of_Courses" that only has one field that lists each of the
courses within the company. (This is not linked to anything as of right now)

The List_Of_Courses table needs some way to indicate what trade and what
position are relevant to that course. My guess would be that a given course
might apply to more than one trade and vice versa; perhaps you need a
RequiredFor table with fields Course (link to Courses), Trade (link to
List_Of_Trades), and Position (link to List_Of_Positions). You'ld put a record
in this table for each combination of trades and positions which require the
course.

You'll need one more table to record who is assigned to which course. Since
each employee might take several courses, and each course will involve several
employees, you need a new table with fields for Empl # and Course. You may
also want fields for the date that the employee takes the course, comments, a
field indicating satisfactory completion, etc.

You would then create a query joining Employees to RequiredCourses joining by
Trade and by Position. This query could be used as the rowsource of a combo
box to display the courses required for this employee.