Advice on table structure/reporting

[KristenD]

I am on my last piece for my database and I am having the most trouble with this last piece. What I have created is a database which tracks employee skill levels, OSHA and state licenses. The last part of the database is the certifications that an employee may or may not hold. And here is where I am running into issues.

Some certifications have an expiration, so do not. Some are state specific, some are not. Some are a combination of expirations and state specific, some are not.

I am trying to figure out the best way to do this and everything I try it just does not work out the way I want it to. The certifications are extraneous to the rest of the database. They are not required (sometimes depending on the owner/GC of a project a cert may be required because of the scope of work). My question is what is the best way to "create" this piece and use it for reporting? Would a datasheet work best for inputting the data?

Thanks in advance!

 

[MarkK]

You need a table for each real-world thing, so
1) you need a cert table, which is the definition--apart from any person--of what the certification is. What are the dimensions of an independent cert object? Name, maybe how long it lasts, if it expires, etc...
2) Then you need a person table, for obvious reasons.
3) Then you need the important table, CertPerson, which joins the two, above, and defines how the cert is related to the person, when it was earned, when it expires, who the granting body was, how the person performed earning the cert.

With this structure you can have a cert form with a subform showing all the people who hold that cert, and a person form that shows all the certs that person holds. This is a three table solution and is commonly called a Many-To-Many relationship.

hth

 

[Solo712]

I am on my last piece for my database and I am having the most trouble with this last piece. What I have created is a database which tracks employee skill levels, OSHA and state licenses. The last part of the database is the certifications that an employee may or may not hold. And here is where I am running into issues.

Some certifications have an expiration, so do not. Some are state specific, some are not. Some are a combination of expirations and state specific, some are not.

I am trying to figure out the best way to do this and everything I try it just does not work out the way I want it to. The certifications are extraneous to the rest of the database. They are not required (sometimes depending on the owner/GC of a project a cert may be required because of the scope of work). My question is what is the best way to "create" this piece and use it for reporting? Would a datasheet work best for inputting the data?

Thanks in advance!

It looks like you need a separate table for Certifications that describes them as you do here. The table will have an ID, name, description of some sort and it will specify the renewal cycle for those certifications that expire and their scope, i.e. state-wide, federal, etc. Then you need another table will link employees and certifications. I don't think there is more to it than this.

Best,
Jiri

 

[KristenD]

Currently my table structure is:

tblEmp
EmpID
EmpName
Status
Type
CraftCode

tblCertification
CertID
CertDescription

tblEmpCert
EmpCertID
EmpID
CertID
CertDate
CertExp
CertState
CertNumber
CertAgency

But not all of the table EmpCert will be filled out. For example, CPR has two distinctive issuing agencies with 2 different expiration dates but First Aid has NO distinctive issuing agency and has a 2 year expiration date. Neither one is state specific. But ATSSA is state specific and but has the same expiration date regardless of the state.

When I create the query to create the form and everything is entered, I want to make sure once I create the reports that since all the certifications are different that it won't screw up any reporting because only certain portions of the fields are filled out.

 

[MarkK]

State might belong in the Cert table, or perhaps in both. A common practice in an order system is that a price is stored in the product table, but is copied to the order details record when an item is added to the order. Then you can change the price in the product record at some future date, but the price in the order, of course, doesn't change. In this respect, price is in fact a discrete dimension of both objects.

So even if 95% of some certs will come from your home state, add State to Cert, copy it to CertPerson where applicable, and change it manually in CertPerson in the 5% or cases the people were certified out of state.

And in a query, you can pull state in from both tables and apply logic to show the one if the other is not present.

hth