relationship question (1 Viewer)

sawindows

New member
Local time
Tomorrow, 07:28
Joined
Jul 4, 2018
Messages
5
Hi all, I have a database which I use to track the status of employees for building site inductions. I have many employees and when these guys go into a building as a contractor they need to have an induction which they complete and these last for periods of time before they need to be completed again....this side of the data base I have sorted.

However each site induction it completed on a system which as en employer I need to provide compliance items to keep my accreditation up. I cant work out how to create a relationship from the induction system to keep track of the compliance items and there status, dates etc. Please help

I think I need a many to many relationship??
 

Ranman256

Well-known member
Local time
Today, 16:58
Joined
Apr 9, 2015
Messages
4,339
Well each would have a table.
Not knowing the the data elements,I can't guess at the design.
 

sawindows

New member
Local time
Tomorrow, 07:28
Joined
Jul 4, 2018
Messages
5
Hi, I have 3 tables on the inductions side. Employees linked to the inductions table and linked to the inductions table I have the records table which records the records of the inductions completed by the employees. All one to many relationships

The induction table has an induction ID (Prim Key), Induction name, company name, induction type etc. It also has the system in which the induction is completed on. An example of a system name is Greencap. This is also the system that i need to track compliance items for.

Compliance items that i need to keep track of include public liability, insurance, company policies which I need to submit with expiry dates.

Hope this helps...
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Jan 23, 2006
Messages
15,364
sawindows,
Welcome to the forum. Readers need more info about your "business" in plain, simple English. That is a context for your post that deals with who, what, when, why... in non database terms.
What sort of accreditation(s) are involved?
What "data" do you record for each?

You could tell us about a day in your business, or this is where employee accreditation fits....

Good luck.
 

sawindows

New member
Local time
Tomorrow, 07:28
Joined
Jul 4, 2018
Messages
5
Hi jdraw, My business is a cleaning business. We send employees to buildings or sites which are governed by safety plans and each employee that sets foot on the property needs to have completed an on line induction through the buildings own online system.

As an employer providing employees to work on these sites I must provide the building manager with certain compliance items to become an accredited contractor. These compliance items may include company policies, insurance, employee insurance etc. All of these items are valid for a period of time or have expiry dates which I need to track.

When my company is compliant i can have employees complete there online inductions. If i do not have up to date information submitted to the induction system my status will become non- compliant.....Not good!

Onto my database:
In the inductions table, each induction is completed by the employee on an online system and it is for this system that I need to track the compliance items. This is where i need suggestions on the tables and relationship set up
 

Cronk

Registered User.
Local time
Tomorrow, 07:58
Joined
Jul 4, 2013
Messages
2,770
At this stage, I can see 3 entitities - Sites, Employees and Inductions. There should be a table for each. Assuming the key field in tblSites is SiteID and in tblEmployees it is EmpID, then tblInductions would have key field InductionID and foreign keys SiteID and EmpID


There would be one to many relationships from both tblEmployee and tblSites to tblInductions based on key fields in the former two tables and foreign keys in tblInductions.


You need to provide more information about compliance matters eg is insurance on the employee or the site.
 

sawindows

New member
Local time
Tomorrow, 07:28
Joined
Jul 4, 2018
Messages
5
Hi Cronk,
You are basically correct on my set up on this site of the database. To manage the inductions for each employee i have 3 tables. employees, Inductions and records

I do not need virtually any information on the site so i have kept the information on the site to a minimum and put this in the induction table.

All three of these tables are link by 1 to many relationships. One employee to many inductions. Please see attached link

Note all of the items that are recorded for this database to date are for individual employees i.e. an employee has a record of when they completed an induction.

Now I need to keep information as an employer to maintain my compliance for the induction system. I figure I already have the information about the induction in the induction table, I now want to create a table to monitor the compliance items submitted for the induction system. However the system name is not a primary key

In the inductions table I have the name of the system. For this system i need to link a record that I have submitted documents for compliance.

An example would be:
A new building is won to clean. The building manager says that I need to register for their induction system and I need to provide 3 documents. Company workers insurance cover (expiry date 10/9/2018) Company drug policy (exp date 30/7/2018) and public liability insurance (exp date 2/8/2018). Once I have submitted this information, as a company I am now compliant for that system and my employees can complete inductions and work on site.

I need to track the compliance items for that system. If they go out of date our company is not compliant and our workers can no longer go on site
 

Attachments

  • database relships.xlsx
    10.6 KB · Views: 39

Cronk

Registered User.
Local time
Tomorrow, 07:58
Joined
Jul 4, 2013
Messages
2,770
In your table structure (in the attached file), the information in the tblInduction relates the the Site, and tblRecords has induction information ie date completed for each employee.


It might be fine for you now but to anyone else coming in cold, or even to you some years in the future, it will be confusing IMO.



You also mentioned multiple compliance documents for each site. If this is what you mean by "Compliance System Name", the data should be in a separate table with the foreign key SiteID.


Incidentally, DateExpired (ExpiryDate?) is superfluous. This can be derived by DateCompleted plus ValidForMonths (MonthsValid?)



Also, please don't have spaces in your table field names - it will save you a lot of problems down the track with queries and in any code.
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Jan 23, 2006
Messages
15,364
I'm just looking at the thread again. I have some questions similar to Cronk.
For clarity, is it you who gets some sort of Liability Insurance and Employee Insurance for your Employees and you provide a statement of your Drug Policy such that when you get a new Job/Client they can be assured you and your company meets all compliance requirements.
Or does each new Job/Client require you to have specific insurance and other documents specific to that Company?

What exactly does Compliance involve? eg Policy Number, Insurance Company, Effective Coverage....

Is it similar to Bonded? see https://www.bankrate.com/financing/insurance/what-it-means-to-be-bonded-licensed-insured/

Another thing to consider is that Access does not like spaces in field names --you will encounter syntax errors at some point. Better to use alphabetics and underscore "_" only in field and object names.
 

Cronk

Registered User.
Local time
Tomorrow, 07:58
Joined
Jul 4, 2013
Messages
2,770
For me, the compliance requirement is explained in the second last para of #7.

The contract company has to supply proof of having workers compensation insurance, public liability and a drug policy, before it can have its workers on the site of its client, in the example given. Other client sites might have different compliance requirements eg environmental sustainability, gender/race equality policies.

I suggested a related to hold details of these compliance documents such as date supplied, date of currency.

Incidentally, I will only use underscores to separate field names where a following word is preceded by capitals eg IRS_Reference. However, that's my style. I'm neither going to defend it nor try to convince anyone else to change.
 

Users who are viewing this thread

Top Bottom