setting criteria problem

[Lori2836 via AccessMonster.com]

Can someone help? I am creating a training database. I have 10 tables - 1
for each set of Work Instructions. One set of instructions has 34 different
procedures....the table has a column for employee name and then one for each
work instruction number (to enter date training was taken) and another column
to be checked if the employee needs the training. Therefore there is a total
of 69 columns (employee, instruction date, instruction needed)....see below

EMPLOYEE DQP1 DQP1 DATE DQP2 DQP2 DATE DQP3 DQP3 DATE

This is getting way too large.....I can't add the criteria on the separate
select query lines....there are too many. Need to say "give me all employees
that need this training (check box is checked for each DQP) and date field is
blank.

Hopefully this is making sense.......help! How can I get the results I
need.....and maybe there is an easier way.........

 

[kingston via AccessMonster.com]

It sounds like you need to normalize the data (basically, redefine the data
tables to eliminate redundancy). Here is a data structure that might work:

Basic tables -
tblEmployee: EmpID, Name, HireDate...
tblWorkInstructions: WkID, Description...
tblProcedures: ProcID, Description...

Linking tables -
tblEmpTrain - EmpID, WkID, Required (Yes/No), Completed (Date)
tblWorkProc - WkID, ProcID, SequenceNo...

Thus, you won't have 10 work instructions tables. Do you see how you'd be
able to record and extract information more easily from this type of
structure?

 

[Lori2836 via AccessMonster.com]

Thanks Kingston. Your response sounds like it would work, but how would I
create a query that would show the employee (there are 70 of them) and all of
the work instructions they need training in.
So much data gets confusing. I have one group of instructions that have 208
separate procedures. Some employees will only need to be trained in 6 of the
208 that need to be listed and others will need to be trained in 100 of them.
There was a previous training database created here, on a much much smaller
scale, where there was a required column for each procedure and they would
just check the box for required employees........I'm just not sure how to go
about making sure there is a checkbox for each employee for each procedure....
...

Hope I'm not too confusing!

It sounds like you need to normalize the data (basically, redefine the data
tables to eliminate redundancy). Here is a data structure that might work:

Basic tables -
tblEmployee: EmpID, Name, HireDate...
tblWorkInstructions: WkID, Description...
tblProcedures: ProcID, Description...

Linking tables -
tblEmpTrain - EmpID, WkID, Required (Yes/No), Completed (Date)
tblWorkProc - WkID, ProcID, SequenceNo...

Thus, you won't have 10 work instructions tables. Do you see how you'd be
able to record and extract information more easily from this type of
structure?

Can someone help? I am creating a training database. I have 10 tables - 1
for each set of Work Instructions. One set of instructions has 34 different

[quoted text clipped - 12 lines]

Hopefully this is making sense.......help! How can I get the results I
need.....and maybe there is an easier way.........

 

[kingston via AccessMonster.com]

I made some assumptions about your data that may not be correct. I'm trying
to convey the need for normalization with an example that you'll probably
have to modify for your needs. In this case, I'd modify the training
information table:

tblEmpTrain: EmpID, WkID, ProcID, Required (Yes/No), Completed (Date)

Thus, you could filter by an individual employee to see what training he/she
needs or has completed. You could filter by a Work Instruction or by a Work
Instruction and a Process to see who has had the training and who needs it.
You might not need the field Required at all if you make it a rule to not
include unnecessary employee/training records (i.e. every record means that
the employee needs the training).

Thanks Kingston. Your response sounds like it would work, but how would I
create a query that would show the employee (there are 70 of them) and all of
the work instructions they need training in.
So much data gets confusing. I have one group of instructions that have 208
separate procedures. Some employees will only need to be trained in 6 of the
208 that need to be listed and others will need to be trained in 100 of them.
There was a previous training database created here, on a much much smaller
scale, where there was a required column for each procedure and they would
just check the box for required employees........I'm just not sure how to go
about making sure there is a checkbox for each employee for each procedure....
...

Hope I'm not too confusing!

It sounds like you need to normalize the data (basically, redefine the data
tables to eliminate redundancy). Here is a data structure that might work:

[quoted text clipped - 17 lines]

 

[Lori2836 via AccessMonster.com]

How is the one "Required" field going to span across all of the possible
procedures all employees may need to be trained in. In the current,
smaller database there is a table as such:

Employee Hire Date Position DQP1.1 Req'd DQP1.2 Req'd DQP1.3
Req'd

And in the Required columns are checkboxes where someone goes in and checks
the DQP's each employee needs training in........then they can run a query
off this table that says show me the training needed by employee........where
the Req'd field is TRUE (checkbox is checked) and the DQP fields (dates) are
null (training hasn't been done yet).

I can't figure out how to use your "Required" field for all.........

I made some assumptions about your data that may not be correct. I'm trying
to convey the need for normalization with an example that you'll probably
have to modify for your needs. In this case, I'd modify the training
information table:

tblEmpTrain: EmpID, WkID, ProcID, Required (Yes/No), Completed (Date)

Thus, you could filter by an individual employee to see what training he/she
needs or has completed. You could filter by a Work Instruction or by a Work
Instruction and a Process to see who has had the training and who needs it.
You might not need the field Required at all if you make it a rule to not
include unnecessary employee/training records (i.e. every record means that
the employee needs the training).

Thanks Kingston. Your response sounds like it would work, but how would I
create a query that would show the employee (there are 70 of them) and all of

[quoted text clipped - 15 lines]

 

[kingston via AccessMonster.com]

What I'm suggesting is a totally different way of storing the data. Do more
research on your own as I don't think I'm doing a good job of explaining data
normalization. If you look at the way you currently store data, there is an
entry for every possible employee/procedure combination. That is an
inefficient way to store data unless you need to actively record negatives (e.
g. an employee does not require a specific training). It also has many other
drawbacks and you're running into them.

I'm suggesting you make a list of only required training after you
restructure your tables as I described before. The list will be much shorter
and easier to manage.

EmpID, WkID, ProcID, Completed (Date)

Joe, 100, 1, Null - record indicates training is needed, lack of date
indicates training is incomplete

Joe, 100, 2, 11/1/2006 - record indicates training is needed, date indicates
training is complete

Jim, 110, 5, 1/2/2005...

You'd never create a record of unnecessary training unless you need to keep
track of such data. If you do need to keep track of negatives, add the field
Required (Yes/No) and make a record for every single possible combination.
In simple terms, what you're doing is turning your table into a long list
(and more tables).

How is the one "Required" field going to span across all of the possible
procedures all employees may need to be trained in. In the current,
smaller database there is a table as such:

Employee Hire Date Position DQP1.1 Req'd DQP1.2 Req'd DQP1.3
Req'd

And in the Required columns are checkboxes where someone goes in and checks
the DQP's each employee needs training in........then they can run a query
off this table that says show me the training needed by employee........where
the Req'd field is TRUE (checkbox is checked) and the DQP fields (dates) are
null (training hasn't been done yet).

I can't figure out how to use your "Required" field for all.........

I made some assumptions about your data that may not be correct. I'm trying
to convey the need for normalization with an example that you'll probably

[quoted text clipped - 15 lines]

 

[Lori2836 via AccessMonster.com]

Thanks Kingston. I need to go back to the people requesting this monster. I
believe they want it in the way I'm trying to structure it. I realize its
way too much data. I understand what you are saying, but think they want to
be able to open a table and click on what each person needs in one sweep.
I'll go back to the requestors and explain the dilema here.

What I'm suggesting is a totally different way of storing the data. Do more
research on your own as I don't think I'm doing a good job of explaining data
normalization. If you look at the way you currently store data, there is an
entry for every possible employee/procedure combination. That is an
inefficient way to store data unless you need to actively record negatives (e.
g. an employee does not require a specific training). It also has many other
drawbacks and you're running into them.

I'm suggesting you make a list of only required training after you
restructure your tables as I described before. The list will be much shorter
and easier to manage.

EmpID, WkID, ProcID, Completed (Date)

Joe, 100, 1, Null - record indicates training is needed, lack of date
indicates training is incomplete

Joe, 100, 2, 11/1/2006 - record indicates training is needed, date indicates
training is complete

Jim, 110, 5, 1/2/2005...

You'd never create a record of unnecessary training unless you need to keep
track of such data. If you do need to keep track of negatives, add the field
Required (Yes/No) and make a record for every single possible combination.
In simple terms, what you're doing is turning your table into a long list
(and more tables).

How is the one "Required" field going to span across all of the possible
procedures all employees may need to be trained in. In the current,

[quoted text clipped - 16 lines]

 

[kingston via AccessMonster.com]

You're welcome. The data should be stored the way I explained, but it can be
presented in the manner you described (with a crosstab query). If the
structure must be the way you described, it's probably best to do this in
Excel. However, I suspect that this started as an Excel file, and you were
told to migrate this over to Access.

Thanks Kingston. I need to go back to the people requesting this monster. I
believe they want it in the way I'm trying to structure it. I realize its
way too much data. I understand what you are saying, but think they want to
be able to open a table and click on what each person needs in one sweep.
I'll go back to the requestors and explain the dilema here.

What I'm suggesting is a totally different way of storing the data. Do more
research on your own as I don't think I'm doing a good job of explaining data

[quoted text clipped - 29 lines]