too much data

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!


Pat Hartman (MVP) wrote:
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]

Thanks for any suggestions!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200702/1

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!

--
Phil

On Feb 8, 7:10 pm, "Pat Hartman \(MVP\)" please no
wrote:
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" u23986@uwe wrote in messagenews:6d7fe28caafb9@uwe...

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!

--
Message posted viahttp://www.accessmonster.com

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