Should I use Access as front end for users or develop a web app?

[tazui1982]

Hi,
This is my first forum post here, been following you guys for a few months now since I first started learning Access. I'm currently building an employees' skill database at medium-size manufacturing site with over 1,600 employees. The database is currently being used by 5-9 people at one time. Basically, at my company in order to be eligible to work at a particular workstation one must have a particular skill to perform tasks. So first, the supervisor logs in and check employee's profile to see if employee has skills. If yes, they work, if not, supervisor calls trainers to coach employees. On average trainers coach roughly 45 people, then register training records on the system. There are a few other simple tasks being performed on the system and most of information is being done on excel files.






My question is should I move to SQL Server, which company has anyway, or stay with Access with back-end file on shared folder and front-end on user's computers? Does it make sense for me to learn to build a website and build web based app for this job instead?

Would really appreciate all the feedback.
I'm currently using Access 2016-32 bit.
SQL Server 2012

P.S: I'm a Training Manager with over 12 years of experience in Human resources, so, no, I have no technical training background and have very basic knowledge of relational databases as well. All I know about databases right now was acquired from learning several videos off Youtube and of course reading your forum posts.

[John_G]

This is a relatively small database, with only a few users, so in my opinion you can stay with MS Access for both.

I would modify your relationship diagram somewhat, though:

Do not link Employee to TrainingRecord - instead link TrainingRecord to TrainingRequest on RequestID. My reasoning there is that the Training Record is really more relevant to the TrainingRequest than to the Employee. The way you have it, you could (in theory) get training for an employee without having to request it. This might violate a "business rule" of "no training without a request.

This will result in a one-to-one relationship between TrainingRequest and TrainingRecord (actually it could be one-to-many, if an employee had to take the same training more than once).

So now you would make the additional changes:

• No relationship between tblModel and tblTrainingRecord (redundant)
• No Relationship between tblTrainingRecord and tblProcess (redundant)
• Remove fields TrainingType, ModelID_FK, and ProcessID_FK from tblTrainingRecord. They are redundant (already in tblTrainingRequest), and could theoretically lead to inconsistent data.

The rest of it looks fine.

You won't have to worry about it until a bit later, but with a little bit of effort, you can set things up so that your users don't have to worry about knowing Access - all they see is forms and reports. You can:

• Deactivate Access Special Keys (Ctrl-G, F11 etc)
• Disable the + sign on startup (requires a VBA sub which lots of us have)
• Hide the Ribbon
• Hide the Navigation Pane

But get the whole thing working properly, first. Proper design is critical, and you are well on the way to accomplishing that.

Good luck with you project - don't hesitate to come back here for more ideas.


What are the datatypes of your fields ExtraDocs and DailyRecord?

[June7]

If everyone has workstations connected to the company server, why even consider a web-based app?

Advise separate fields for names - FirstName, LastName

Are line leaders employees?

I presume ExtraDocs and DailyRecord are attachment type fields. Be aware that storing large files in tables can quickly use up Access 2GB file size limit.

[tazui1982]

Sounds good, i will work on design again!

Both extraDocs and dailyRecords fields are for attachments, required by external QC auditors, usually a 2 to 5mb pdf file(s) so yea access file can probably fill up its 2gb limit within 6 months or less.

Actually, my goal is to build a more ambitious resource planning database, which will store employees data, their skill levels and readiness to fulfill daily customer orders, therefore im curious about starting early with SQL, but with my current technical skill, i will take your advise and stay on Access. I am on mobile phone right now, will try to elaborate and post pics a bit more tomorrow after work. Thanks!

[tazui1982]

Hi,
I am not sure why its web based. When I first raised this request with IT department 7 months ago, they built it for me without actually going into details why they did what they did. Back then I wanted them to create an Access app but they refused. I did have a few concerns back then because the database they built looked a lot like a 2d database. I suspect the database its a poorly designed because i find at least 1000 duplicates last month, using Access query too.

[June7]

IT refused? You probably need to know why. What platforms did they use - SQLServer backend and what frontend? SQLServer can be a backend to Access frontend. Are they going to roadblock your implementation of Access db on server? Will they object to an Access frontend installed and running on each user's workstation? Does someone higher up need to back you up?

[tazui1982]

If everyone has workstations connected to the company server, why even consider a web-based app?

Advise separate fields for names - FirstName, LastName

Are line leaders employees?

I presume ExtraDocs and DailyRecord are attachment type fields. Be aware that storing large files in tables can quickly use up Access 2GB file size limit.

Hum, I would prefer to not go through pain of splitting names on excel then append using query.
Yes Line Leaders are employees. We currently have following titles: "Operators", "Line Leaders", "Technicians", "PFT Leaders", "Shift Managers", operators being the lowest title of course and shift manager is the highest. Would you advise to keep them in the same tblEmployees? By the way trainers are also employees, most of them have "Operators" title as well.

[tazui1982]

This is a relatively small database, with only a few users, so in my opinion you can stay with MS Access for both.

I would modify your relationship diagram somewhat, though:

Do not link Employee to TrainingRecord - instead link TrainingRecord to TrainingRequest on RequestID. My reasoning there is that the Training Record is really more relevant to the TrainingRequest than to the Employee. The way you have it, you could (in theory) get training for an employee without having to request it. This might violate a "business rule" of "no training without a request.

This will result in a one-to-one relationship between TrainingRequest and TrainingRecord (actually it could be one-to-many, if an employee had to take the same training more than once).

So now you would make the additional changes:

• No relationship between tblModel and tblTrainingRecord (redundant)
• No Relationship between tblTrainingRecord and tblProcess (redundant)
• Remove fields TrainingType, ModelID_FK, and ProcessID_FK from tblTrainingRecord. They are redundant (already in tblTrainingRequest), and could theoretically lead to inconsistent data.

The rest of it looks fine.

You won't have to worry about it until a bit later, but with a little bit of effort, you can set things up so that your users don't have to worry about knowing Access - all they see is forms and reports. You can:

• Deactivate Access Special Keys (Ctrl-G, F11 etc)
• Disable the + sign on startup (requires a VBA sub which lots of us have)
• Hide the Ribbon
• Hide the Navigation Pane

But get the whole thing working properly, first. Proper design is critical, and you are well on the way to accomplishing that.

Good luck with you project - don't hesitate to come back here for more ideas.


What are the datatypes of your fields ExtraDocs and DailyRecord?

Thank you for your tips: I made several changes as suggested. Please take a lookAttachment 30268

1. So is it OK for me to create Training Request Form (For supervisors to fill in when training is needed), then create another form "Training Record Form" for trainers to complete supervisors' request, all using Parent & Child relationship? Or how else would I see Model and Process from "Training Request Form" right?
2. Should I merge "Line Leaders" and "Trainers" into tblEmployees since they are operators anyway.


Thanks

[John_G]

Please take a lookAttachment 30268

It appears the attachment is not there or is the wrong type. An attempted download says it is a .php file. Can you try it again?

[tazui1982]

Can you see it now? I can see it on my screen.

[John_G]

1. So is it OK for me to create Training Request Form (For supervisors to fill in when training is needed), then create another form "Training Record Form" for trainers to complete supervisors' request, all using Parent & Child relationship? Or how else would I see Model and Process from "Training Request Form" right?

You could do that. One way might be to put a button on the training request form (and you could make it so that only trainers see that button) which would bring up the Training Record Form to be completed. You could add a DateCompleted field to tblTrainingRequest and the request form to that Supervisors would know it has been completed.

2. Should I merge "Line Leaders" and "Trainers" into tblEmployees since they are operators anyway.

That makes sense. You would have to add fields into tblEmployee (YesNo type would suffice I think) to indicate which employees on on each "List", and use those fields when selecting Line Leaders or Trainers from Combo boxes. It would simplify maintenance of tblEmployee.

Can you see it now? I can see it on my screen.

Yes it worked this time - I'll take a look at it.

[tazui1982]

IT refused? You probably need to know why. What platforms did they use - SQLServer backend and what frontend? SQLServer can be a backend to Access frontend. Are they going to roadblock your implementation of Access db on server? Will they object to an Access frontend installed and running on each user's workstation? Does someone higher up need to back you up?

They simply said that I needed a web based app, which was much better than Access. I suspect they use SQL for backend and a website for front end. No, they're not going to roadblock anything for me, all they did was letting me into development environment so I could upload my data tables. They would not object to having front ends because all machines have Office 2016 with Access anyway. Last I spoke to them, about 20 times as well since June lol, they agreed, I think just so I backed away and let them do their own things.

[John_G]

It's looking good. The only change I would make is to take EmpUniqueID_FK out of tblTraining and put it in tblTrainingRequest. In fact, you have to do that, otherwise your Requestors cannot indicate who the training is for!

In tblTrainingRequest, RequestorID_FK is an FK to what? I assume it's tblEmployee, so you will need a relationship there, too.

2. Should I merge "Line Leaders" and "Trainers" into tblEmployees since they are operators anyway.

After I looked at your latest diagram, you might want to reconsider that. There are fields in those two tables that are not in tblEmployee (e.g. TrainersCompanyID).
Also, you might want to expand the Trainers information a bit at some point, to indicate what type of training they can give, because right now if someone asks "who have we got that can give training A?", you can't answer the question. You might need this information in the case of vacations or long-term absences.

[tazui1982]

You could do that. One way might be to put a button on the training request form (and you could make it so that only trainers see that button) which would bring up the Training Record Form to be completed. You could add a DateCompleted field to tblTrainingRequest and the request form to that Supervisors would know it has been completed.



That makes sense. You would have to add fields into tblEmployee (YesNo type would suffice I think) to indicate which employees on on each "List", and use those fields when selecting Line Leaders or Trainers from Combo boxes. It would simplify maintenance of tblEmployee.



Yes it worked this time - I'll take a look at it.

Sounds good. Let me work again on my database, and if you don't mind i'll attach Access file here, I just need to modify all data and actual names so I don't actually disclose any confidential info on public forums. Thanks in advance for your help.
By the way, hiding button from Sups and only trainers can see it, it's out of my skills I tried that once, following youtube videos but was really complicated and failed at least 10 times. I only managed to learn to create a login screen with username and pass.

[tazui1982]

It's looking good. The only change I would make is to take EmpUniqueID_FK out of tblTraining and put it in tblTrainingRequest. In fact, you have to do that, otherwise your Requestors cannot indicate who the training is for!

In tblTrainingRequest, RequestorID_FK is an FK to what? I assume it's tblEmployee, so you will need a relationship there, too.



After I looked at your latest diagram, you might want to reconsider that. There are fields in those two tables that are not in tblEmployee (e.g. TrainersCompanyID).
Also, you might want to expand the Trainers information a bit at some point, to indicate what type of training they can give, because right now if someone asks "who have we got that can give training A?", you can't answer the question. You might need this information in the case of vacations or long-term absences.

Actually TrainerCompanyID and LineLeaderID are exactly the same as EmployeeID, they all have the same thing. The reason I split them was because I needed to give them user access type 1 2 3 or 4 when I was learning to allocate user access.

Trainers in my company can give generic training to all employees, in fact that's all they allowed to do, anything more difficult would require engineers to step in so if 1 or more trainers are absent others can fill in. Do I still have to add training type to trainers?

According to Company's policy only line leaders can request a training, therefore RequestID_FK was meant to link to LineLeaderID, yea, now I realize how bad it is. Let me try to merge all line leaders and trainers into tblEmployees.