[beginner] - Migrating personnel management system from EXCEL to ACCESS. (1 Viewer)

[AndyC88]

Good afternoon everyone, this forum seems like a really great place to ask for some guidance, so I'll begin...!

To give some background, the way my employer manages it's personnel (for certain tasks) is pretty dire. A lot of it is managed using excel, and even though we use MS Sharepoint, the gremlins of version control, multiple sources of the truth etc often come to the fore. I've spent many hours thinking how certain things could be done better, and the more I read and learn about access the more I'm convinced it's the right tool for the job.

The core function of the database would be as an employee database. (For approximately 500-600 staff). This part I think is fairly easy, and I have build a table, with a split form view that works well to view / edit employee data.

The more complicated bit, is that each employee has a fairly specific set of software / equipment requirements for when they are assigned to a particular job. The total list of software / equipment is over 200 fields.

Previously this was done using excel, but as you can imagine it's not user friendly and prone to errors.

I've has mutiple attempts at making this work:

1. Using multiple check boxes across all the fields (BAD - I realise now this was just trying to emulate excel)
2. Using mutiple sub-forms with a join table (equipment requirements - linked to employee ID and Equipment ID) - this seemed to work ok
3. I *think* this now the best solution - if you look at access_3 - all the equipment requirements on one table, with the equipment grouped into categories. What I've been trying to do is filter the subform using command boxes but from what I can see the filter applies to the main form and not the subform.

I hope that makes sense - I'm happy to share the database but will need to sanitise it first for any sensitive info.

I would really appreciate any advice on whether I'm barking up the wrong tree, or if anyone has any ideas on how it could be done better I'd love to hear them!

Thanks,

 

[plog]

Forms are the last thing to work on when building a database, so put those aside for now. What you need to do is correctly build and structure your tables. That process is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

Read up on it. Work on a few tutorials then apply what you learn to your data. Do that by actually building the tables you need with the appropriate fields with the correct data types. Then complete the Relationship Tool in Access. Bring in all your tables to it, expand them so it shows all the fields, JOIN the tables appropriately then take a screenshot. Post that screenshot back here. That's the image we need to help you get started.

 

[AndyC88]

Thanks for the quick reply! Attached is my relationship table so far. I've read a fair few articles on data normalisation and I think I understand the concept - it's just a different way of thinking about the data (that I've never done before!).

I'm about 4 days into having used access - it's been a steep learning curve so far but I feel like I'm getting there...!

 

[plog]

That doesn't look too bad, but 2 things:

1. Tables with only one real piece of data (autonumber primary keys aren't real data) shouldn't exist. So all those tables on the left (and TBL_Equipment_Type) should go. Instead, in Tbl_PIMS you just store the value that the ID goes to, not just the ID to the table.

2. Redacted field names are a bad sign. Field names should be generic and understandable to everyone. Since you blurred them it makes me think you are storing actual data in field names. I understand data can be sensitive, but that's just it, the data is sensitive, not the name of the field the data goes into.

Back to your initial post:

The more complicated bit, is that each employee has a fairly specific set of software / equipment requirements for when they are assigned to a particular job. The total list of software / equipment is over 200 fields.

Did you mispeak when you said you have a 200 field table? Did you instead mean Tbl_Equipment has 200 values for Equipment_Name field?

I think ultimately you are asking how to build a form to populate Tbl_Equip_Requirements, right?

 

[Isaac]

FWIW, I sometimes blur field names and database or sharepoint list names because they reveal too much about where I work. The nature, department (since I've already revealed I work at a big bank, the rest could be derived from the name). Like if you worked for Procurement and had a table ProcurementRecords or something.

 

[Minty]

1. Tables with only one real piece of data (autonumber primary keys aren't real data) shouldn't exist. So all those tables on the left (and TBL_Equipment_Type) should go. Instead, in Tbl_PIMS you just store the value that the ID goes to, not just the ID to the table.

I'm not sure I agree with you on this.
In these type of lookup tables, I normally add an Active flag and also a sort order column.
This allows me to set items as inactive but retain the data for historical reporting, and also display them in a specific order in the combo.

 

[AndyC88]

That doesn't look too bad, but 2 things:

1. Tables with only one real piece of data (autonumber primary keys aren't real data) shouldn't exist. So all those tables on the left (and TBL_Equipment_Type) should go. Instead, in Tbl_PIMS you just store the value that the ID goes to, not just the ID to the table.

2. Redacted field names are a bad sign. Field names should be generic and understandable to everyone. Since you blurred them it makes me think you are storing actual data in field names. I understand data can be sensitive, but that's just it, the data is sensitive, not the name of the field the data goes into.

Back to your initial post:



Did you mispeak when you said you have a 200 field table? Did you instead mean Tbl_Equipment has 200 values for Equipment_Name field?

I think ultimately you are asking how to build a form to populate Tbl_Equip_Requirements, right?

Thanks plog,

1. So the tables on the left - I thought as the data value stored in PIMS is just an integer, this will reduce the size of the database and is better practice? Rather than having 500 or so employees with the corresponding text data fields saved?

2. As for the blurred names - promise I’m not storing data in there! It is the field names which are sensitive as it would make it very obvious for whom I work.

So originally I had a 200-field table, with YES/NO fields against the employee ID (LSN). I since realised that’s bad practice and changed it to what you see above!

Yes and no to your last question - the end goal would be to be able to export the data with name, employee ID, branch etc on the left, and then which programs they require as the fields. (As ultimately this will be imported into excel).
Unless I can think of a better way of doing it.

But yes - main obstacle at the moment is how to efficiently and effectively display the equipment-requirements.

Thanks,

 

[mike60smart]

Good afternoon everyone, this forum seems like a really great place to ask for some guidance, so I'll begin...!

To give some background, the way my employer manages it's personnel (for certain tasks) is pretty dire. A lot of it is managed using excel, and even though we use MS Sharepoint, the gremlins of version control, multiple sources of the truth etc often come to the fore. I've spent many hours thinking how certain things could be done better, and the more I read and learn about access the more I'm convinced it's the right tool for the job.

The core function of the database would be as an employee database. (For approximately 500-600 staff). This part I think is fairly easy, and I have build a table, with a split form view that works well to view / edit employee data.

The more complicated bit, is that each employee has a fairly specific set of software / equipment requirements for when they are assigned to a particular job. The total list of software / equipment is over 200 fields.

Previously this was done using excel, but as you can imagine it's not user friendly and prone to errors.

I've has mutiple attempts at making this work:

1. Using multiple check boxes across all the fields (BAD - I realise now this was just trying to emulate excel)
2. Using mutiple sub-forms with a join table (equipment requirements - linked to employee ID and Equipment ID) - this seemed to work ok
3. I *think* this now the best solution - if you look at access_3 - all the equipment requirements on one table, with the equipment grouped into categories. What I've been trying to do is filter the subform using command boxes but from what I can see the filter applies to the main form and not the subform.

I hope that makes sense - I'm happy to share the database but will need to sanitise it first for any sensitive info.

I would really appreciate any advice on whether I'm barking up the wrong tree, or if anyone has any ideas on how it could be done better I'd love to hear them!

Thanks,

Hi Andy
Can you give us an example of 1 Employee and their associated Equipment/Software requirements?

 

[AndyC88]

FWIW, I sometimes blur field names and database or sharepoint list names because they reveal too much about where I work. The nature, department (since I've already revealed I work at a big bank, the rest could be derived from the name). Like if you worked for Procurement and had a table ProcurementRecords or something.

Yes - exactly this! Not deliberately being secretive or anything.

 

[plog]

But yes - main obstacle at the moment is how to efficiently and effectively display the equipment-requirements.

That's not what I thought, nor what you initially posted. You posted about building a form to get data into the system, not out of it.

If this thread is about getting data out of the system then I suggest you mock up what you want the spreadsheet to look like.

 

[AndyC88]

That's not what I thought, nor what you initially posted. You posted about building a form to get data into the system, not out of it.

If this thread is about getting data out of the system then I suggest you mock up what you want the spreadsheet to look like.

Apologies ... trying to fix everything at once and not taking it step by step - I'm also awful at explaining myself

To try and explain a bit better - the end result is getting the data out of the system in a way which can be displayed meaningfully. As this is the end result, I will create a mock-up of what I'd like to get out of it.

The building of the form is a means to an end - I'm not certain I'm approaching it in the right way so I'll hold off on explaining that any further until I've created the mock up.

Thanks for the input so far - you all seem very helpful and if I can get even close to what I can see in my head, I'll be very happy!

 

[Pat Hartman]

1. Tables with only one real piece of data (autonumber primary keys aren't real data) shouldn't exist. So all those tables on the left (and TBL_Equipment_Type) should go. Instead, in Tbl_PIMS you just store the value that the ID goes to, not just the ID to the table.

Plog and I disagree on this. I use a different method which I have included but keeping these simple lists in a table facilitates data entry and eliminates typos. This is a mini-app and you can import the two forms, two reports, and two tables into any of your applications. The idea for this app came to me over 40 years ago and although it has evolved slightly over time and is now implemented in Access/ACE or Access/ODBC, it is a way to standardize your lookup tables. The tables this app manages are simple lookups and can include an Active flag so that you can keep unused items in the table but prevent them from being used for new records or changes to existing records.

Naming Standards
1. Names should ONLY include letters (upper or lower case), numbers (0-9) and the underscore(_) ALL other characters, including embedded spaces, should never be used when constructing object names. Some people like CamelCase which uses caps to separate words and others prefer the_underscore. I don't like the_underscore because I'm lazy and I don't like to use the shift key when I type. Too many years of typing COBOL all in upper case I guess. The other advantage to CamelCase is when you type me.customerid followed by a space, Access converts it to Me.CustomerID assuming you used case when you defined the field name originally. This is a simple typo check as you are writing code.
2. I disagree completely with using ID as the PK name for all your tables. the PK should include some part of the table name so you end up with unique PK names for each table. I also use ID as the suffix for the PK when the PK is an autonumber. So, looking at everything in my database, SomethingID is always a PK or a FK.

 

[Isaac]

I was going to say I do sometimes have lookup tables, like "Colors" > red, blue, yellow

 

[AndyC88]

Pat, thank you for that explanation. It is absolutely to facilitate data entry, and also their values may change / get added / deleted over time so I assumed it's easy to do this via a linked table than go through and edit each record individually?

Thanks for the tips on the naming conventions - will be sure to go through and amend.

I've mocked up a crude excel screen grab of what the current system is similar to - the reality is more complex but that is it in it's basic form.

Very open to suggestions on how this could be done better, and indeed whether access is the correct tool or should we just stick with excel?

 

[mike60smart]

Andy

Access is the ideal tool for this.

A Division has Many Branches
Each Branch has Many Employees

Each Employee has Multiple pieces of Equipment.

Your Horizontal display of Many Columns for each piece of Equipment is Excel

In Access each piece of Equipment becomes a Record in a table

 

[Pat Hartman]

so I assumed it's easy to do this via a linked table than go through and edit each record individually?

No, as i explained. When you have an app with lots of lookup tables, it gets to be a PITA to make a maintenance form for each of them. Look at the app. All the lookup tables end up in a single table.

 

[Pat Hartman]

Very open to suggestions on how this could be done better, and indeed whether access is the correct tool or should we just stick with excel?

Access is the correct tool but not if you don't take off your Excel hat. You will be very disappointed with Access and it will be far more work than Excel if you don't normalize your tables.

Just because your data entry forms don't look like your Excel workbook, doesn't mean that you can't create reports that look like your Excel version.