Just starting out - confused by tutorials

[DHFE]

You have a junction table for employees and their required certs (as well as one for the employee and certs achieved)?

So at the moment I've got one table for Employee info (names, department, etc), one table for Certificate info (Name, Length of Validity) and a junction table called 'Records' which uses the EmployeeID from the Employee Info, the CertificateID from Certificate Info, and also has the Completed Date for the certification.

Edit:
The IDs are both set as one-to-many

[CJ_London]

Your junction table also needs a date obtained field which when you have a formula to add the length of validity to the obtained date tells you when it expires and needs to be renewed

[pbaldy]

Post 11 was moderated, I'm posting to trigger email notifications.

[Micron]

So at the moment I've got one table for Employee info (names, department, etc), one table for Certificate info (Name, Length of Validity) and a junction table called 'Records' which uses the EmployeeID from the Employee Info, the CertificateID from Certificate Info, and also has the Completed Date for the certification.

That is fine for cert compliance but doesn't seem to allow you to id which certs anyone requires - assuming that is not all of them.

[DHFE]

Your junction table also needs a date obtained field which when you have a formula to add the length of validity to the obtained date tells you when it expires and needs to be renewed

Sorry I'm not sure I understand.

My junction table has a date - ​"Completion Date" this is the date the certification was passed, which the user will enter.

The expiry date is calculated in a query

[DHFE]

That is fine for cert compliance but doesn't seem to allow you to id which certs anyone requires - assuming that is not all of them.

Wont that be able to be calculated from the expiry dates? e.g. a query with a criteria of if expirydate<today, or today-30/60/90 days to see ones which are due to expire. As well as ones which have no expiry i.e. have not yet been carried out

Edit:
This is what I meant by dependent combo boxes.

For example
The first allows the user to select the specific certificate they want to check, and the second allows me to pick either ones which are in date, expiring, expired or not yet completed; once these are selected the query is run by the user and they get a list
Similarly the users can select a specific employee and check if they have any certificates which are expiring or have expired.

[Micron]

You're not understanding my point: There are 40 certs. I need 10 of them, not 40. Next month things change and now I need the same 10 plus 5 more. How are you going to capture the new 5 requirements? You can't do that with records of past training because the new requirements are not going to be in the empl table, nor the table of certs (which is all certs in the system) nor the records of past training. Nor should the requirements for me be in any of those tables.

[mike60smart]

Can you upload a copy of the Matrix you use for Employees Training?

[DHFE]

There are 40 certs. I need 10 of them, not 40. Next month things change and now I need the same 10 plus 5 more.

That scenario is not currently required. If it gets to that stage then I'll make a more detailed database.

Seriously, right now it's just a spreadsheet with hundreds of columns split into different certs and their completed and expiry dates; and rows, split between different employees.

All I'm trying to do is to make all that data easier to read, rather than having to scroll through everything.

Like I said, the users aren't that computer literate, thay had to come to me regarding conditional formatting in the spreadsheet. Then one of them opened my re-made spreadsheet from their email didn't understand why it was read-only.

All the users really need to know is; who does or doesn't have a specific certification, and when they are about to expire. It doesn't need to be more complicated than a list of people, certificates and dates.

[DHFE]

Can you upload a copy of the Matrix you use for Employees Training?

Not really, it would take to long for me to remove all the confidential data.

Each row is a different employee
The first columns are their name, department, job title, employed (y/n)
Then the following columns are the different certs - 2 columns for each cert. One the completed date, and one the expiry date.

[mike60smart]

Can I ask some basic questions regarding your Employees

Does the Employee remain in the same department for the duration of their Employment?

Or, are they sometimes switched to different Departments.

Also, would the employees expect to have the same Job Title for the duration of their Employment?
Or, can they get promoted and take on a new Job Title?

[DHFE]

They can switch departments and job titles

[mike60smart]

OK

This means you will need to cater for these options to maintain a History of Certificates.

[DHFE]

OK, This means you will need to cater for these options to maintain a History of Certificates.

We are not currently maintaining a history - just if their current certification is valid or not.

[mike60smart]

Ok then you surely have enough information supplied to create a database?

Have you even looked at the example Db that Bert uploaded?