too much data

[Lori2836 via AccessMonster.com]

Can someone help? I'm being asked to create a training database. There are
129 employees and 14 sets of work instructions. One set has 208 individual
instructions, and they are asking for 4 more columns to be added for each one.
The final outcome should be that they open a table for each set and check
which employees will need to be trained in which instruction, the other
columns are date, revision, revision date and legend. Then a query should
be setup where when opened, it will show only those instructions needed to be
trained on.....by saying "true" in the check box column and "is null" in the
date column. There is so much data here, I'm not sure how to proceed.

Is this do-able?

Thanks for any suggestions!

 

[Pat Hartman \(MVP\)]

It is doable but you need to remove your spreadsheet hat and put on your
relational database hat. In a spreadsheet you add "columns" when you add
new instructions but in a relational database, you add "rows". The
difference is substantial. With rows, no programming needs to take place.
With columns everything changes, your forms, your reports, your queries, and
any code you use also.

Do some reading on normalization to get some understanding of the
transformation from columns to rows. Don't get hung up on formatting the
data entry form. It will be very difficult to retain the spreadsheet look
because you will now be managing the instructions with a subform into which
selections are added rather than using checkboxes as they are used to.

Don't weaken and make the table look like a spreadsheet, you will mire
yourself in hundreds of queries (one for each instruction type) to extract
information whereas with the proper structure, you will need only one query
that takes a parameter.

 

[Lori2836 via AccessMonster.com]

Thanks Pat. I'm trying to get my arms around it. Will there be a way for
these people to view it as they want to? Such as:

Employee DQP 11 DQP 11 Date DQP 11 Rev DQP 11 Rev Date DQP
11 Legend


Where under the DQP 11 column there will be a check box........and they want
it for each employee, so they can look across and just check which ones each
employee will need training in?

I've started reading about normalization and am I correct in assuming I will
need only one table with a list of all the instruction #'s, date, rev and rev
date and then a separate table for employees? How would I join two such
tables in order to view the way they want it?

It is doable but you need to remove your spreadsheet hat and put on your
relational database hat. In a spreadsheet you add "columns" when you add
new instructions but in a relational database, you add "rows". The
difference is substantial. With rows, no programming needs to take place.
With columns everything changes, your forms, your reports, your queries, and
any code you use also.

Do some reading on normalization to get some understanding of the
transformation from columns to rows. Don't get hung up on formatting the
data entry form. It will be very difficult to retain the spreadsheet look
because you will now be managing the instructions with a subform into which
selections are added rather than using checkboxes as they are used to.

Don't weaken and make the table look like a spreadsheet, you will mire
yourself in hundreds of queries (one for each instruction type) to extract
information whereas with the proper structure, you will need only one query
that takes a parameter.

Can someone help? I'm being asked to create a training database. There
are

[quoted text clipped - 15 lines]

Thanks for any suggestions!

 

[Pat Hartman \(MVP\)]

There is no way to make a bound form that looks like your spreadsheet. Your
form will look more like:
Employee .... employee stuff
|-----------------------------------------------| <-- subform
|DQP 11 -- Date -- rev -- RevDate -- Legend |
|DQP 12 -- Date -- rev -- RevDate -- Legend |
|DQP 13 -- Date -- rev -- RevDate -- Legend |
| * |
|-----------------------------------------------|

The subform will have only as many rows as you would have had checked
checkboxes.

Thanks Pat. I'm trying to get my arms around it. Will there be a way for
these people to view it as they want to? Such as:

Employee DQP 11 DQP 11 Date DQP 11 Rev DQP 11 Rev Date
DQP
11 Legend


Where under the DQP 11 column there will be a check box........and they
want
it for each employee, so they can look across and just check which ones
each
employee will need training in?

I've started reading about normalization and am I correct in assuming I
will
need only one table with a list of all the instruction #'s, date, rev and
rev
date and then a separate table for employees? How would I join two such
tables in order to view the way they want it?

It is doable but you need to remove your spreadsheet hat and put on your
relational database hat. In a spreadsheet you add "columns" when you add
new instructions but in a relational database, you add "rows". The
difference is substantial. With rows, no programming needs to take place.
With columns everything changes, your forms, your reports, your queries,
and
any code you use also.

Do some reading on normalization to get some understanding of the
transformation from columns to rows. Don't get hung up on formatting the
data entry form. It will be very difficult to retain the spreadsheet look
because you will now be managing the instructions with a subform into
which
selections are added rather than using checkboxes as they are used to.

Don't weaken and make the table look like a spreadsheet, you will mire
yourself in hundreds of queries (one for each instruction type) to extract
information whereas with the proper structure, you will need only one
query
that takes a parameter.

Can someone help? I'm being asked to create a training database.
There
are

[quoted text clipped - 15 lines]

Thanks for any suggestions!

 

[Lori2836 via AccessMonster.com]

I have a table with Employees, a table with the Instructions which includes
the dates and revs, etc...
but how would I tie those together? Or are you saying that this would be
done in the form? For each employee, I would go into the subform and enter
the date, etc for a particular work instruction. So it would be correct if I
went back to the requestors and said that this cannot be done in the fashion
they are wanting it in?

Some days I really think a Walmart greeter would be the best job in the world!

There is no way to make a bound form that looks like your spreadsheet. Your
form will look more like:
Employee .... employee stuff
|-----------------------------------------------| <-- subform
|DQP 11 -- Date -- rev -- RevDate -- Legend |
|DQP 12 -- Date -- rev -- RevDate -- Legend |
|DQP 13 -- Date -- rev -- RevDate -- Legend |
| * |
|-----------------------------------------------|

The subform will have only as many rows as you would have had checked
checkboxes.

Thanks Pat. I'm trying to get my arms around it. Will there be a way for
these people to view it as they want to? Such as:

[quoted text clipped - 43 lines]

 

[Phil]

Hi Lori ...

Yes, this is "do-able" as I created an application something like this
a long time ago using Paradox 4.02/DOS. (circa 1995)

One question comes to mind, though ... Does each employee have
a unique employee number or some other unique identifier? Without
seeing your data, I believe this table design to be a 1-to-many table
relationships.

Or possibly 1 - to - Many - to - Many table relationship.

For example you could have an "Employees" table linked to each set
of "work instruction(s)" tables.

Think of the Employee table personal employee table (ie. payroll
number, name, division, etc) and the work instructions table will hold
the individual instructions. The trick here is to figure out what
unique identifier will "link" these tables together.

Once you get all of this "lined out", setting up queries should be
relatively easy.

MS Access has the capability to link tables together "graphically".
This will give you a clearer picture as to how your table
relationships are setup.


Just out of curiosity, what type of file format is the data currently
in?


Hope this helps and good luck!

 

[Phil]

Hi Lori ...

Please pardon the intrusion ... But I felt the need to offer some
assistance ...

I have a table with Employees, a table with the Instructions which includes
the dates and revs, etc...

but how would I tie those together? Or are you saying that this would be
done in the form?

You're almost there... That's the trick... Finding a unique ID'er
that will "link" both tables together - and yes you could do it easily
in a form. Does each employee have a unique Identifier, (i.e. payroll
or employee number)??

From what you describe, this data set appears to be a 1 -to- Many
table relationship.

Some days I really think a Walmart greeter would be the best job in the world!

I've been in application development for 15 years - I can relate!

 

[Pat Hartman \(MVP\)]

You actually need three tables because you have a many-to-many relationship.
You have employees and you have instructions. To link the two you need a
third table that contains the primary keys of both tables and whatever other
attributes are defined by the intersection of the two tables. So from your
earlier example:
|----------------------------------------------------------| <-- subform
|EmpID -- DQP 11 -- Date -- rev -- RevDate -- Legend |
|EmpID -- DQP 12 -- Date -- rev -- RevDate -- Legend |
|EmpID -- DQP 13 -- Date -- rev -- RevDate -- Legend |
| *
|
|----------------------------------------------------------|

tblEmployeeInstructions:
EmployeeID (primary key field1, foreign key to tblEmployee)
InstructionID(primary key field2, foreign key to tblInstructions)
InstructionID
InstructionCompleteDate
Rev
RevDate
Legend

This is a presence or absence situation. If there is a row in this table,
the employee needs instructions. If there is no row, he doesn't need
instructions. Use tblInstructions to populate a combo to allow users to
only choose valid values.

I have a table with Employees, a table with the Instructions which includes
the dates and revs, etc...
but how would I tie those together? Or are you saying that this would be
done in the form? For each employee, I would go into the subform and
enter
the date, etc for a particular work instruction. So it would be correct
if I
went back to the requestors and said that this cannot be done in the
fashion
they are wanting it in?

Some days I really think a Walmart greeter would be the best job in the
world!

There is no way to make a bound form that looks like your spreadsheet.
Your
form will look more like:
Employee .... employee stuff
|-----------------------------------------------| <-- subform
|DQP 11 -- Date -- rev -- RevDate -- Legend |
|DQP 12 -- Date -- rev -- RevDate -- Legend |
|DQP 13 -- Date -- rev -- RevDate -- Legend |
| * |
|-----------------------------------------------|

The subform will have only as many rows as you would have had checked
checkboxes.

Thanks Pat. I'm trying to get my arms around it. Will there be a way
for
these people to view it as they want to? Such as:

[quoted text clipped - 43 lines]

Thanks for any suggestions!

 

[Lori2836 via AccessMonster.com]

Hi Phil.......thanks for your help.

I have an access table for Employees, a table for Work Instructions.
The employee has an Employee ID #, which I created. Would I add the
Employee ID # to the Work Instructions table? You would think I've never
worked in Access before by the way this has got me mystified.....but I guess
its the amount of data and the way the requestor actually wanted to be able
to view it that has me messed up. They wanted it in "spreadsheet" form so
they could just open up a table for each set of work instructions, which
would have the employees listed on the left and all the instructions in
columns across with check boxes......then they would just check which
instructions each employee would need to be trained in......then query so it
would show only those instructions where the boxes were checked. It was
done previously for a different group, but there were only something like 7
instructions.........

Hi Lori ...

Yes, this is "do-able" as I created an application something like this
a long time ago using Paradox 4.02/DOS. (circa 1995)

One question comes to mind, though ... Does each employee have
a unique employee number or some other unique identifier? Without
seeing your data, I believe this table design to be a 1-to-many table
relationships.

Or possibly 1 - to - Many - to - Many table relationship.

For example you could have an "Employees" table linked to each set
of "work instruction(s)" tables.

Think of the Employee table personal employee table (ie. payroll
number, name, division, etc) and the work instructions table will hold
the individual instructions. The trick here is to figure out what
unique identifier will "link" these tables together.

Once you get all of this "lined out", setting up queries should be
relatively easy.

MS Access has the capability to link tables together "graphically".
This will give you a clearer picture as to how your table
relationships are setup.

Just out of curiosity, what type of file format is the data currently
in?

Hope this helps and good luck!

--
Phil

Can someone help? I'm being asked to create a training database. There are
129 employees and 14 sets of work instructions. One set has 208 individual

[quoted text clipped - 9 lines]

Thanks for any suggestions!

 

[Pat Hartman \(MVP\)]

If you were dealing with only 7 instructions, I could show you how to make a
bound form "look" like a spreadsheet but be based on a normalized structure.
But, each "column" that you want to pivot up to a row requires an additional
join and I'm certain the join limit is around 20-30 so 200+ is out of the
question.

Before you get all bogged down in creating an update form, try creating some
queries that you'll need against both schemas.

If they will ever need a report showing all people assigned a particular
instruction, the denormalized structure will require 200+ queries or VBA to
create the appropriate query on the fly whereas with the normalized
structure, you can use a SINGLE querydef that takes a parameter and it will
return details regarding any or all instructions.

Hi Phil.......thanks for your help.

I have an access table for Employees, a table for Work Instructions.
The employee has an Employee ID #, which I created. Would I add the
Employee ID # to the Work Instructions table? You would think I've never
worked in Access before by the way this has got me mystified.....but I
guess
its the amount of data and the way the requestor actually wanted to be
able
to view it that has me messed up. They wanted it in "spreadsheet" form so
they could just open up a table for each set of work instructions, which
would have the employees listed on the left and all the instructions in
columns across with check boxes......then they would just check which
instructions each employee would need to be trained in......then query so
it
would show only those instructions where the boxes were checked. It was
done previously for a different group, but there were only something like
7
instructions.........

Hi Lori ...

Yes, this is "do-able" as I created an application something like this
a long time ago using Paradox 4.02/DOS. (circa 1995)

One question comes to mind, though ... Does each employee have
a unique employee number or some other unique identifier? Without
seeing your data, I believe this table design to be a 1-to-many table
relationships.

Or possibly 1 - to - Many - to - Many table relationship.

For example you could have an "Employees" table linked to each set
of "work instruction(s)" tables.

Think of the Employee table personal employee table (ie. payroll
number, name, division, etc) and the work instructions table will hold
the individual instructions. The trick here is to figure out what
unique identifier will "link" these tables together.

Once you get all of this "lined out", setting up queries should be
relatively easy.

MS Access has the capability to link tables together "graphically".
This will give you a clearer picture as to how your table
relationships are setup.

Just out of curiosity, what type of file format is the data currently
in?

Hope this helps and good luck!

--
Phil

Can someone help? I'm being asked to create a training database. There
are
129 employees and 14 sets of work instructions. One set has 208
individual

[quoted text clipped - 9 lines]

Thanks for any suggestions!

 

[Lori2836 via AccessMonster.com]

If it were only 7 instructions, I wouldn't be having a problem. It's the
amount of data and really no common factor between the employees and the
instructions. I asked if they had a listing of what job positions were
required to take what training and thought I could use that, but they
apparently have nothing. Just a matter of going in and checking a box under
a certain instruction when a supervisor tells them to. If I were to create
a form and have the top show the employees and a subform at the bottom with
all instructions and pertinent info such as dates and revs, how would I
connect the two?

Thanks so much for all of your help!

If you were dealing with only 7 instructions, I could show you how to make a
bound form "look" like a spreadsheet but be based on a normalized structure.
But, each "column" that you want to pivot up to a row requires an additional
join and I'm certain the join limit is around 20-30 so 200+ is out of the
question.

Before you get all bogged down in creating an update form, try creating some
queries that you'll need against both schemas.

If they will ever need a report showing all people assigned a particular
instruction, the denormalized structure will require 200+ queries or VBA to
create the appropriate query on the fly whereas with the normalized
structure, you can use a SINGLE querydef that takes a parameter and it will
return details regarding any or all instructions.

Hi Phil.......thanks for your help.

[quoted text clipped - 58 lines]

 

[Phil]

HI Lori ...

Hi Phil.......thanks for your help.

You're welcome ...

I have an access table for Employees, a table for Work Instructions.
The employee has an Employee ID #, which I created. Would I add the
Employee ID # to the Work Instructions table?

Yes, sure... That way you could "link" the Employees table to the
Work Instruction tables. The "Employee ID#" would be the "Primary
Keyed" Field allowing you to have unique records in the "Employee"
table. Also by having the Employee ID# in the Work Instructions table
would create (once you linked the tow tables inside an Access form)
a 1 -to- Many tables relationship.

In other words for one record in the Employees Table you could have
many records in the Work Instructions table. Get it?

You would think I've never
worked in Access before by the way this has got me mystified.....but I guess
its the amount of data and the way the requestor actually wanted to be able
to view it that has me messed up.

Don't worry ... Many developers have had a need to "step back" and
"look at the big picture" ... It works out better that way to think
the problem more throughly. Once you get this table relationship
finalized, running queries should be relatively easy.


Hope this helps and good luck!

 

[pamoori]

It is doable but you need to remove your spreadsheet hat and put on your
relational database hat. In a spreadsheet you add "columns" when you add
new instructions but in a relational database, you add "rows". The
difference is substantial. With rows, no programming needs to take place.
With columns everything changes, your forms, your reports, your queries, and
any code you use also.

Do some reading on normalization to get some understanding of the
transformation from columns to rows. Don't get hung up on formatting the
data entry form. It will be very difficult to retain the spreadsheet look
because you will now be managing the instructions with a subform into which
selections are added rather than using checkboxes as they are used to.

Don't weaken and make the table look like a spreadsheet, you will mire
yourself in hundreds of queries (one for each instruction type) to extract
information whereas with the proper structure, you will need only one query
that takes a parameter.

Can someone help? I'm being asked to create a training database. There
are
129 employees and 14 sets of work instructions. One set has 208
individual
instructions, and they are asking for 4 more columns to be added for each
one.
The final outcome should be that they open a table for each set and check
which employees will need to be trained in which instruction, the other
columns are date, revision, revision date and legend. Then a query
should
be setup where when opened, it will show only those instructions needed to
be
trained on.....by saying "true" in the check box column and "is null" in
the
date column. There is so much data here, I'm not sure how to proceed.

Is this do-able?

Thanks for any suggestions!

I have a way of designing databases by writing simple sentences. If
you are interested
in a solution, please post the same at my forum. I can help you with.

With the best regards,
Venkat

www.englishtouml.com
Now UML Data Models Better And Faster