Relationship between tables ?

[RainLover]

Thanks RainLover that database should be a help when i can get the hang of what's going on. I see all your forms are based on queries ?....i've never done it this way before.

How do you do it? Do you struggle over SQL statements in VBA or what.

I only have Access 2003. Can you save your DB in that version.?

I can get 2007 if needed. Just let me know. If it is 2010 then that is the end of me.

As for now it is time to sleep. It is after 1:00 AM here.

 

[Emma35]

Don't stay up trying to get your head around this mess....get some sleep. I'm attaching the 2003 version just to ruin your day tomorrow

Thanks again

 

[jdraw]

I have looked at your database briefly and find that the table names do not convey much info. spikepl gave advice in his post about Positions having certain requirements and employees occupying positions. I think his advice is on the mark.

As I mentioned earlier I find it advantageous to design the tables before getting into queries, forms etc.

It seems you are dealing with Employees, JoBPositions, Position Training Requirements , Training Courses and Employee Training.
While I don't understand your SOP and your TrainingRecords, I have put together a draft model that may be of help to you. I may have missed part of your requirements, but am providing this to help clarify the purpose of your tables. It may give you some things to consider with your design.

Of course there are many other attributes that could/should be assigned to the entities -- for example -- DatePositionRequirementsWereLastChecked...

I have called the table EmployeeHasTraining, but it could be renamed to identify TrainingNeeded/scheduled etc.

As Rainlover says, a data model of the subjects and relationships is a great tool to show what the business is about; how the data are related; and provides a great focus for communicating with users and developers.

Good luck with your project.

 

[RainLover]

You are very close. More when I wake up.

If you can read up on the Properties Master and Chield fields.

You find these in the Sub Forms.

 

[RainLover]

I can't help but feel that the information contained in the Table tblTrainingRecords has more to do with the SOPs rather than anything else.

tblTrainingRecords should contain the Name of the Training, Date Completed, Date of Refresher. That sort of thing.

The SOPs should contain what is required for each SOP. The How when where why of it all.

Would you agree with that.

Did you do the research I suggested in my previous post.

I don't agree with jdraw's relationships, but I shall not say they are wrong. He may be understanding things better than me. Time will tell.

 

[Emma35]

jdraw thanks for your help. This database isn't actually the same as personnel records....it's simply a means of letting the production managers see who's trained on what. Things like DOB, next of kin and other personal stuff are kept in the personnel dept.
My tables are as follows -

tblTrainingRecords is just a record of the details relating to each job the person is trained on. Things like the date the training started/finished, trainers name, refresher training given and manager involved. A lot of the fields are just checkboxes.

tblTrainedSOPData is to record which SOP's (Standard Operating Procedures) the staff member has been familiarised with. There are several of these relating to each job. If you look at frmAddNewRecord, i've added a subform which contains a combo box which allows the user to select several SOP's to be added to the record.

RainLover....it's actually the reverse. The SOP's table will contain several entries for each record in the Training table.
Yes i saw your post about the subforms thing. I understand how it works but i think that the reason i can't get it to work this time is because my relationships are still not correct.

Thanks

 

[RainLover]

Emma

Did you read up on Master Child Properties.

This is why your Form does not work.

Is there anything about this advice that you do not understand. Can't you find the info.

Access Help would be a good place to start.

Fix this before fixing your relationships. You can go back to them later.

 

[RainLover]

jdraw

I don't mean to interfere with your advice.

Your differing view is most helpful.

 

[jdraw]

Emma,

I was not suggesting Personnel records etc per se. I was simply trying to identify clearly the subjects you are dealing with and how they are related. You can assign attributes to the entities/tables as appropriate. My intent was only to separate the entities and name them so their purpose was clear.

In my view if you start programming/interfacing before you have a data model that can support the business area and can satisfy some test data/sample general info, you will get lost in the details of Access and syntax.

If you read posts in most forums, people do not do their design effort up front. To me that is data model, normalization, naming and playing "stump the model with some general test data". It isn't the only approach, but it is one tested and tried that works.

If you step back and take a broad view, personnel is dealing with the "employee", and you are dealing with that same "employee" and his her qualifications for positions and the requirements of those positions. Information in the 2 systems may be brought together to understand this "employee" and his/her training, job history, training plans....

Good luck with your project.

 

[Emma35]

RainLover.....yes i researched master/child fields and with a bit of tweaking have managed to create the links between the forms (at least i think they're right). I'm now getting some error messages when i try to enter some test information using the form AddNewRecords. I'm attaching the database again....i'd be grateful if you could have a look.

jdraw....apologies , i thought you meant you didn't see where i was going with the various tables etc. I'm pretty much an amateur at this and i certainly appreciate both you guys taking the time to help.

 

[jdraw]

I did a little googling and found a free data model at
http://www.databaseanswers.org/data_models/jobs_skills_and_users/index.htm
The requirements/specifications for the model are at
http://www.databaseanswers.org/data_models/jobs_skills_and_users/facts.htm

There are a number of free models on a variety of topics at the base site.
Perhaps they will be of assistance to you in future.

Good luck and --no apologies necessary.

 

[RainLover]

Emma

A sub form should be linked to the Main form. Primary Key as the Master and the Foreign Key as the Child.

You have not addressed this issue.

 

[RainLover]

Emma

In Post #17 I mentioned a Database that I wrote to demonstrate Relationships.

Have a look at the Properties Box in design view. It will show you Master Child Links.

 

[Emma35]

Hey guys....sorry i was off work sick today. I'm going to jump back on it in the morning.
Thanks again

 

[Emma35]

I think i might have cracked it !. Have a look at the latest version i've attached. I changed the tables around a little and everything aseems to be ok....although that's what i thought last time

 

[RainLover]

Ever Table should have a Primary Key

The best Primary Key to use is autonumber.

 

[Emma35]

Ok how's this ?

 

[RainLover]

Yes

Much better.

So are we finished or do you still have issues.

 

[Emma35]

No i think that might be it....the tables look good and the data entry form seems to be working fine. It's almost going home time over here now but i'll be back at it this coming Mon. Thanks very much for all your help and indeed everyone who posted in the thread. I'm sure i'll be back soon with something else.

Thanks again guys
Emma xx

 

[RainLover]

Good to hear you are making progress.

Best wishes for the balance of the project.