Good Afternoon (Or perhaps morning?) my friends in the U.K and the world,
I'm an extreme novice to Access (No VBA or SQL experience) working at a call centre in Melbourne, Australia and have created a database as a replacement for our current personnel files. I have done so through a lot of trial and error, many tutorials and hours of reading posts on this very forum.
I have a few specific questions but mostly I just would like general advice if you'd be so kind. Firstly I'll outline what the db is:
Currently each staff member here has an 'Agent Development file' which is an individual Excel spreadsheet containing the following sheets:
Personal Details (Name, DOB, EmployeeID, Emergency contacts etc.)
Discussion Record (A running table of relevant conversations or notes written by their manager)
Absence Record (A table of all their absences, reason for absence, type of absence etc)
Performance Management (Basically just some info HR likes us to keep track of for more serious conversations.)
We have several hundred staff and each of them has an individual spreadsheet containing this information which I'm sure many of you are as horrified about as I was. On top of that because of the (Unnecessary) Macro's and splicers used each file takes up to 30 seconds to load. They are incredibly slow and inconvenient but we've grown so fast that nobody's stopped to fix it until now.
I have spent weeks powering through immense frustration to teach myself how to use access and feel that I've done okay so far - I am still a long way off rolling this db out for use but have reached a point where I'm worried it won't cut it.
What I have so far:
6 tables containing the same information as the above sheets as well as tables for Team Numbers, Operations managers and other such information which I have made in order to Normalise my tables which I understand from you all is quite important
I have 15 Queries which are really the heart of what I'm making. Effectively they all just search for different information based on selections made in combo boxes (I'll explains specifics further down.)
Finally I have 11 Forms - 3 of which are sub-forms and 5 of which are pop-up 'input' forms with Data Entry mode enabled. The primary navigation form is called EmployeeSelector and I have it set up with two combo boxes:
Team - Select from the list of teams (Straight from a table)
Employee - Select from a list of agents with the TeamID selected in the above.
From here the user presses a button which opens the EmployeeDetails form which shows info using a query to select the info of what's selected in Employee combo box. This form also contains all the subforms I mentioned above which also use queries to search for information based on the combo box selections.
So far so good right, it all works despite my many mistakes and it's already much better than the excel versions as far as usability, aesthetics and performance.
Which brings me to my primary concern - Performance.
Given the information provided above I will outline below how much data I expect to have in this db (It just has example data at the moment)
Approx. 500-700 Current Staff each with an EmployeeDetails record (14 Fields, All Short Text, Numbers and Dates effectively)
Anywhere between 10 and 100 discussion records per employee (7 Fields. One long text and some numbers,dates,short text.)
Anywhere between 0 and 100 Absence Records per employee (8 fields, Short text, Numbers, and dates again plus a yes/no field.)
Anywhere between 0 and 50 Performance Management Records per employee. (15 Fields, Dates, numbers, one long and a few short text,)
I have already split the database (Another hot tip I learned from here) and my understanding is that the most efficient way to provide access is to send each user a FE which is fine. I basically want to know, will it function and perform reasonably with this much data? Like I said I haven't used access before and I don't know the limitations.
Additionally I worry about concurrent users - I've read that access supports 255 (I think?) concurrent users but I'm unsure how performance is affected by number of users. I would expect a maximum of 40 concurrent users and a realistic average of 15-20.
I can provided a stripped db if required but I'm moreso wanting some assurance that this will get the job done before I 'Over-commit' as it were. Particularly as I intend to engage professional assistance before going live I don't want to waste any money. I'm also happy to provide any further information if required.
I really appreciate the space to air my concerns and look forward to reading your responses.