Question Creating multifunctional database

[KristenD]

Good Morning!

I am fairly new to Access being away from it for several years. I have been tasked with creating databases to track skills, certifications and licenses for all of our employees. I am in the middle of creating the Skills database for our employees but I was wondering if I could put all of what we are looking to track into one database. Everything would tie back to the employee ID. I would like to have a form that would contain only the Employee Info (Emp ID, Emp Name & Craft Code) and then have 3 command buttons that would link to the different tables for each of the criteria that we are tracking. Is this possible?
Thank you!

 

[jdraw]

Sounds reasonable.
I would suggest a data model. Seems you would have tables for these Entities (as a start)

Employees

SkillsInventory

LicencesInventory

CertificationsInventory

Then junction tables for

EmployeeHasSkills

EmployeeHasCertifications

EmployeeHasLicences

Good luck with your project.

 

[KristenD]

For the Skill Database I actually have 4 tables in order for it track the info needed. Is there a limit on how many tables I can have? The other 2 databases are not as intricate where they may only the need the two tables listed above.

Thanks!

 

[Lightwave]

For MS Access 2007 the limit on the number of tables is governed by a limit on the number of objects in the database, which includes tables, forms, reports, queries , etc . The limit on DB objects is 32,768

Should give you a bit of room to manoeuvre!

 

[KristenD]

Is that the number of records or the total of forms, queries, reports, tables, etc?

 

[jdraw]

Kristen, What I suggested was a minimum set of tables off the "top of my head". You may need more tables because of what information you collect and maintain as well as any concerns(structures) you may need to restrict access to certain information.

I would focus on the Tables and the fields/attributes you want to collect, before moving to Forms and collection techniques. All are important, but table structure is key -- in my view.

Good luck.

 

[Simon_MT]

Jst a thought:

If the Employee has a License - is the Employee Certified? And does the Employee has a Skill?

Simon

 

[KristenD]

The licenses are plumbing and HVAC licenses or CPR, First Aid, welding etc.
Certifications would be along the lines of OSHA 10, Signal and Rigging, Cranes, etc.

The licenses have expiration dates attached to them so it would be used to track for instance a welder in NH has to renew his TIG weld certification so he needs to do some TIG welding in order to renew otherwise he will have to retest through the state.

The certifications until we are 100% in some of them would be used to see if the employee can be transferred as long as he has the correct certification.

The skills are tied to job performance. The employee is rated on skills when he is a new hire, rehired, transfered to a different job, laid off, resignation or termination.

 

[Lightwave]

It's the combined total of all "Objects"

Which are regarded as
Tables
Forms
Reports
Queries
Modules and
Macros

You'd have to go totally crazy to reach this limit. In 10 years of using MS Access I've never heard of anyone in the world hitting this!

Regards number of records - the specification pages I've read don't indicate a limit on the number of records but do say that a database cannot be any bigger than 2gb. It is really really difficult to hit this size unless you include pictures actually within the database.

I have a database with everysingle geographical location in our district at work it contains 289,963 records it only comes to 100MB. Unless you have millions of plumbers on your system you will be ok

 

[KristenD]

Ok, thanks! We have a high turnover rate and that is the only thing i'm worried about. Like last year we had over 800 employees and it could go up or down. It all depends on the amount of work we have and what work we get and the scope of work that is involved in each project.

 

[Pat Hartman]

You will find all the information you need regarding capacity by searching for "specifications" or "limits" in Access help. The entry is too large to post here plus it will be slightly different for each Access version. Tables are not limited by row count. They are only constrained by the total size of the database. I have had Jet tables with several million rows so I don't think 800 will be a problem.

Once you've settled on your data model, you can post it and we'll be happy to review it. Keep in mind that object names including tables and columns should not contain embedded spaces or special characters. Use only a-z, A-Z, 0-9, and the underscore. I prefer CamelCase but some people prefer using The_Underscore to separate words in names. As for forms, I would probably design a main form which shows employee demographic information and contains subforms organized on a tab control that show each of the criteria you are tracking. You might be able to organize the form so that all three subforms are visible at the same time but I like tabs to avoid over crowding.

 

[Lightwave]

Entering 800 new people a year by my rough calculations it will take you about one thousand two hundred and fifty years to reach a million records. I would shift to SQL server then.