Creating My First DataBase Critique

Hi
I would imagine that a Facility would have Many Employees.

Your method of adding more than 1 Employee in the field "AssignedEmployees" is the wrong way to do this.

See the ER Diagram attached. You need a separate table to record "FacilityEmployees"
 

Attachments

  • ER.JPG
    ER.JPG
    93.6 KB · Views: 132
  • Like
Reactions: Kyp
mike60smart said:
Hi
I would imagine that a Facility would have Many Employees.

Your method of adding more than 1 Employee in the field "AssignedEmployees" is the wrong way to do this.

See the ER Diagram attached. You need a separate table to record "FacilityEmployees"
Click to expand...
Gotcha, I added tblAssignedEmployees and recreated the relationship as seen in your screenshot. In tblFacilities, I changed the datatype of "Assigned Employees" to number to agree with the relationship. With this change, would I be able to enter more than one employee in this field?
 
No you need to delete the field "AssignedEmployees" from the Facilities table.
 
Kyp said:
Gotcha, I added tblAssignedEmployees and recreated the relationship as seen in your screenshot. In tblFacilities, I changed the datatype of "Assigned Employees" to number to agree with the relationship. With this change, would I be able to enter more than one employee in this field?
Click to expand...
Can you upload your current database in zipped format?
 
  • Like
Reactions: Kyp
OK so does tblFacility only ever have :-

1 Contractor & 1 Controller
 
mike60smart said:
OK so does tblFacility only ever have :-

1 Contractor & 1 Controller
Click to expand...
The synopsis is:
A facility can have 20, to 30 controllers [PLC's}.
Annually, we upload the active program, saving is with a date/time stamp. This is under normal conditions.
If the active program needs to be changed/modified in any way, I will save the active program without changing the date/time stamp. When I make the changes, again I save the modified program with a new date/time stamp. In the event that I am not available or on another job and I cannot make it the the facility to make the modifications/changes to the active program, I will call out a third party contract person. When modifications are made, it will be at most, 2 employees, or 2 contract technicians.

I am trying to keep up with the programs as well as who/when changes are made.
 
So you are currently responsible for 1 specific Facility or Multiple Facilities ?
 
So it is as follows:-

A Facility has Maintenance carried out by Yourself or Other Employees or Contractors.
 
Kyp said:
about 25 different locations in 3 different states including offshore oil rigs.
Click to expand...
I'm not keeping up with me, each facility has their own techs. I am just the guy that keeps up with all this. (manager)
mike60smart said:
So it is as follows:-

A Facility has Maintenance carried out by Yourself or Other Employees or Contractors.
Click to expand...
Yes, the maintenance for these controllers is usually carried out by the assigned facility tech (total of 2), or myself (just me), and in the event no one within the company is available, I will call out 2 third party techs. to complete the task.
 
What has the field "Company" got to do with the tblFacility details?
Does 1 specific Company own the Facility?
 
So Facility1 is within your Company
Then Facility2 can be owned by another Company
Then Facility3 is within your Company etc...
 
mike60smart said:
So Facility1 is within your Company
Then Facility2 can be owned by another Company
Then Facility3 is within your Company etc...
Click to expand...
That is correct, not including the company I work for, there are 15 different companies with our assets that we have to maintain. We also pickup new companies from time to time.
 
The attached ER is my first attempt at your process.
It is not 100% accurate as there are fields in some tables that need to be deleted.
 

Attachments

  • ER.JPG
    ER.JPG
    129.5 KB · Views: 137
  • Like
Reactions: Kyp
mike60smart said:
The attached ER is my first attempt at your process.
It is not 100% accurate as there are fields in some tables that need to be deleted.
Click to expand...
That is a really big difference from what I had!
I didn't know it was going to be that difficult,
Wanted to start again with something easy! :cautious:

I wish I could hear your thought process while putting ti together! I recon the next step creating queries, forms and reports will be a hoot!

Thanks Mike!
 
See the Conversation I have started with you. Top right of the screen on the Forum
 
You must log in or register to reply here.

Similar threads

P
my first form
Replies
15
Views
909
Pat Hartman
P
H
Beginner Seeking Help: Building an Access Database for Volunteer Fire Department
Replies
9
Views
742
arnelgp
arnelgp
J
New Form Clearing Old Form For PO's
Replies
16
Views
624
Pat Hartman
P
A
Numerical ID vs Alphanumerical ID: practical advice needed on best way forward from where I am
2
Replies
23
Views
1,554
Cotswold
Cotswold
R
Current Inventory Calculations
Replies
6
Views
570
tvanstiphout
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom