I'm setting up and designing a database to track employee's observations. We have a mainframe database and programs that use an Employee Master file (E/M) for payroll and H/R purposes, for which I cannot use in my Access application. Therefore I am designing a simple employee master table within my Access database.

We have many companies and divisions so we have fields in the E/M for company (2 digits) and division (3 digits), as well as an employee number (6 digits). I believe that an employee number is not unique across the company. That is, employee #012345 can exist in Company 01 Division 001 as well as for Company 17 Division 001.

Therefore I will need to set up fields to recognize the employee's company and division numbers.

How should I set up these fields? As text (alphanumeric) or as numeric? Will it make much of a difference later on when I am designing reports? Anything else I should consider in setting up my employee master table?

My general rule of thumb is to store "numbers" like that as text. The only time you'd store them as numbers is if you're going to be doing arithmetic on the value.

The fact that you provided three different values with leading zeroes reinforces my view: you cannot store lead zeroes with numeric values.

Thanks Doug, that is a very good rule of thumb!

As Doug says, Text for sure.

I'd just add that you should probably create a unique three-field Index on the combination of Company, Division and EmployeeNumber. Any of these individually may (will!) have duplicates, but I would assume that the combination should not.