Trouble with Employee database

[swhennen85]

Good evening everyone! I am new to the forums, and pretty new to Access also, so hello!

I am having an issue with a database I am creating. I found a template and am wanting to use the database to handle Employee Attendance/Time Off Requests. I've created several tables, queries, forms, and reports. For the most part I am pretty satisfied with the progress and changes I've been able to make to it. However, now I am at a standstill.

• Before posting this, I found out that I could've created a Web database. I am curious if this is compatible with Sharepoint Services, because I would like to have our HR secretary be able to use both this and the Sharepoint Calendar. Unfortunately, I don't know if she is able to make entries/deletions if it was saved as a Web. If not, then it's no big deal and I can just continue to use what I am now.

• I need to be able to link together the Employee and the number of points each employee accrues. This is dependent on the Attendance/Call off Status Code (Found in Status Code table/form.) Each person should start with 0 points.

• Eventually, I would like there to be some sort of prompt or report that notifies the database user/company manager when an employee(s) reach certain amounts (as specified in the table OFI Schedule)

Example: Employee "Test1 One" has accrued 2 points; Employee is subject for "A verbal warning."

• I've noticed that the Time Off Request is no longer pulling the filters I had specified. I've been trying to play with the code I had specified in the Filters table, but can't seem to get it right.

Any assistance in this would be greatly appreciated!!

[Dal Jeanis]

I wasn't able to figure out your request.

1) web vs regular - that's a "nice to have". if you decide to do it, it will take some work to change, and you can ask for advice. I'd suggest forgetting about it for now, because the other questions you are asking tell me that you need to focus on the application and the database first, before worrying about delivery method.

2) You link those by creating a table that stores the points, and having a foreign key to the employee that the points are for. You haven't said what the rules are, or what the linkage is between those codes and the points, so I'm not going to speculate on what the specifics might look like.

3) That could be a query or report, either scheduled or manual. Pretty easy to do, just define what you're looking for, based upon what information is stored in the database.

4) That's the reason for this absolute rule - back up your work early and often. Every time you make a successful version, back it up. Every time you start for the day, back it up. I use the standard "databasename 2013-0716D.accdb" where D is the fourth backup of the day. I don't keep them forever, but I usually keep the last working version of each day for several weeks, then clean up when I hit major revisions and/or implementations.

[ssanfu]

I looked at your dB last night and also wasn't able to understand your problems. I did see several things that should be addressed. (just my opinion)
These are not in any particular order.... I am doing this from memory.

I've noticed that the Time Off Request is no longer pulling the filters I had specified. I've been trying to play with the code I had specified in the Filters table, but can't seem to get it right.

You have linked the tables on the PKs. (Employees.ID <-> Time Off.ID) This results in a one to one relationship. (probably not what you wanted)

----------

Which brings up ...
In every table the PK is "ID". At the very least, this can be confusing. Take the time to create useful field names.
For instance, you could use "Emp_ID", "EmpID_PK" or "EmployeeID" as the PK for the employee table. For the Time Off table, might use: "TimeOff_ID", "TimeOffID" or "TimeOff_PK". For the corresponding field in a linked table, you could use the suffix "FK". So if the time off table is linked to the employee table, the field names might be:

Employee table
EmployeeID_PK

TimeOff table
TimeOffID_PK
EmpID_FK (link to employee table)

This would be a one to many relationship... one emp can have many time off records.

----------

There should be NO spaces or special characters in object names... except the underscore.

----------

You have lookup FIELDS in the tables. No experienced developer that I have heard of uses lookup fields.
See:
The Ten Commandments of Access ->> http://access.mvps.org/access/tencommandments.htm
The Evils of Lookup Fields in Tables ->> http://access.mvps.org/access/lookupfields.htm

----------

I need to be able to link together the Employee and the number of points each employee accrues. This is dependent on the Attendance/Call off Status Code (Found in Status Code table/form.) Each person should start with 0 points.

This will be easier to fix once the field names/type are corrected. You have the "points" as Text instead of Single. You can do calculations on "Text" numbers but you have to go through a lot of conversion/validation to do the calculation. Easier to just fix the fields.

I would suggest re-evaluating your table design/structure.

[swhennen85]

Dal Jeanis
Expert Windows XP Access 2010 32bit

Join DateMay 2013LocationDallas TXPosts583

I wasn't able to figure out your request.

1) web vs regular - that's a "nice to have". if you decide to do it, it will take some work to change, and you can ask for advice. I'd suggest forgetting about it for now, because the other questions you are asking tell me that you need to focus on the application and the database first, before worrying about delivery method.

2) You link those by creating a table that stores the points, and having a foreign key to the employee that the points are for. You haven't said what the rules are, or what the linkage is between those codes and the points, so I'm not going to speculate on what the specifics might look like.

3) That could be a query or report, either scheduled or manual. Pretty easy to do, just define what you're looking for, based upon what information is stored in the database.

4) That's the reason for this absolute rule - back up your work early and often. Every time you make a successful version, back it up. Every time you start for the day, back it up. I use the standard "databasename 2013-0716D.accdb" where D is the fourth backup of the day. I don't keep them forever, but I usually keep the last working version of each day for several weeks, then clean up when I hit major revisions and/or implementations.

Dal,

Thank you for the feedback. I believe you're right. I'm more concerned about getting the database completed first, but as I saw ssanfu's description below it would appear there are some other things that I need to consider reworking before I go further. However, I did follow you're step 2 and step 3. I was able to create the relationships properly for that and I was able to generate a query just pulling the employee and the total amount of points. I realized that if I included status, there wasn't uniqueness to the query and it would show each employee appearing multiple times.

[swhennen85]

I looked at your dB last night and also wasn't able to understand your problems. I did see several things that should be addressed. (just my opinion)
These are not in any particular order.... I am doing this from memory.


You have linked the tables on the PKs. (Employees.ID <-> Time Off.ID) This results in a one to one relationship. (probably not what you wanted)

----------

Which brings up ...
In every table the PK is "ID". At the very least, this can be confusing. Take the time to create useful field names.
For instance, you could use "Emp_ID", "EmpID_PK" or "EmployeeID" as the PK for the employee table. For the Time Off table, might use: "TimeOff_ID", "TimeOffID" or "TimeOff_PK". For the corresponding field in a linked table, you could use the suffix "FK". So if the time off table is linked to the employee table, the field names might be:

Employee table
EmployeeID_PK

TimeOff table
TimeOffID_PK
EmpID_FK (link to employee table)

This would be a one to many relationship... one emp can have many time off records.

----------

There should be NO spaces or special characters in object names... except the underscore.

----------

You have lookup FIELDS in the tables. No experienced developer that I have heard of uses lookup fields.
See:
The Ten Commandments of Access ->> http://access.mvps.org/access/tencommandments.htm
The Evils of Lookup Fields in Tables ->> http://access.mvps.org/access/lookupfields.htm

----------


This will be easier to fix once the field names/type are corrected. You have the "points" as Text instead of Single. You can do calculations on "Text" numbers but you have to go through a lot of conversion/validation to do the calculation. Easier to just fix the fields.

I would suggest re-evaluating your table design/structure.

Thank you for taking the time to review the DB for me, I do appreciate it. Unfortunately I am still very new to Access. I have done what you mentioned and changed all of the generic "ID" keys to be a bit more specific: Emp_ID, TimeCard_ID, etc.

Instead of using the lookup fields for referencing tables, what would more proper way of reference? I didn't know there were issues with using Lookups. Some of the details from the links you shared make sense, while others not so much only simply due to my lack of knowledge with Access.

[Dal Jeanis]

The main thing about automated lookups in the tables is that they (like a lot of the helpful thngs that Access does) confuse the issue of exactly what you're looking at. If the table is storing the foreign key, but displaying the name field instead, then elsewhere you can end up trying to compare the name against a field that only holds numbers, which is never a good thing. Also, when the wizard autogenerates a form for you, it names the controls (the text box or listbox, for example) the same as the underlying field in the database, so when you later use the name of the field in a macro or in VB, it's not always clear what you're referring to, the control or the value of the field on the table.

But Access makes it very easy to create lots of functionality quickly.

If the lookups are working for you and not confusing you, you don't *have* to fix them. On the other hand, if you leave the foreign key in the table, you can still use a properly designed listbox or textbox to display the value.

Here's the way you'd set up the control:


Suppose you have three tables like this

Table   tblBreeds
brID    PK Autonumber
brName  Text
brNotes memo

Table   tblOwners
owID    PK Autonumber
owName  Text
owPhone Text
owNotes memo

Table   tblPetNames
pnID    PK Autonumber
owID    FK to tblOwners 
brID    FK to tblBreeds
pnName  Text

If you don't have lookups in the tblPetName, then when you look at the table in datasheet view, owId and brId are just numbers.
So, instead of a textbox, you put a combobox on the form. For the sake of the example, let's do brId, and let's call the combo box cboBrId.
Set the properties of the combo box like this:

On the Format Tab -
Column Count  :    2
Column Widths :    0";2"

On the Data Tab -
Control Source:    brID
Row Source    :    SELECT brID, brName FROM tblBreeds;
Bound Column  :    1
Limit To List :    Yes  

On the Other Tab -
Name          :    cboBrId

What that achieves for you is that the combo box cboBrId is bound to the brID field, but it displays the Breed Name, just as if there were a lookup on the table itself, but without any confusion.

[ssanfu]

This is just my opinion....

If you are creating a quick and dirty dB of just yourself, and only you are going to use it, AND you don't want/need a form to enter the data, then you could use lookup fields.

But if other people are going to use the dB, you don't want them to see/edit the tables - you cannot control data integrity or validation. You must use forms. So on a form you create a combo box that is exactly the same as the lookup field. Now you have duplicated effort. And, when you look at the table (debugging), the actual value is hidden.

If the dB gets big enough or useful enough and you want to convert it to SQL Server Express, SQL Server, MySQL Oracle, etc., you will have to "fix" the dB because those servers, etc. do not support lookup fields.

I have never use lookup fields because
1) I came from the XBase world
2) I think lookup fields violate normalization rules.
3) I think it is harder to debug (if required)

And what Dal said

[Dal Jeanis]

I said some more, in post 6 above, to show you how to use the alternative steve suggests - the combo box.

[swhennen85]

I think I understand a bit more what you mean. I see that the affected table is the Time Off table, it has 3 lookup fields and each is pulling from the Time Off Extended Query... I suppose that's wrong and I should have the query be pulling all the data it needs from each table, right?

I had linked the Time Off Form to the Time Off Table for data entry. Let me ask this, if I remove the lookups from the Time Off Table and create a new Time Off Query to pull the data it needs, is it possible to create a new Time Off Form from the new Time Off Query for data entry? Will it still store the records?

[Dal Jeanis]

The critical thing to make sure with regard to making a query updateable is that the query returns the foreign key from the record you want to update, rather than the primary key from the other table. Of course, if you don't include another table in the query, then the issue can't occur at all.
For example:

Select 
   [ID], 
   [Employee], 
   [Date of Submission], 
   [Start Date], 
   [End Date], 
   [Status Code] 
FROM 
   [Time Off];

This query is updateable, because it only includes one table, and doesn't have any aggregation. The ID field cannot be updated, because it's the primary key, but all the others can. Now, if you bind a control on the form to [Time Off].[Employee], but make its RowSource come from the Employees table, then that control can update the [Employee] field on table [Time Off] without any trouble at all. (You just don't use the data sheet view to do it.)

Notice that for that query you could have just used the table itself.

Let's talk about naming conventions. Microsoft's defaults and examples totally suck.

If you want to keep yourself sane, you should make sure that your tables start with tbl (for example), and you don't put any spaces in the middle of the field names. Also, you should be clear what type of data you're storing in any particular field, and don't get it mixed up with related fields.

In this case, [Employee] is actually the [ID] field from the [Employees] table.

Me, I'd change the table name to [tblEmployees] and change the [ID] field to [EmpID], so there's no ambiguity.

I'd also change table [Time Off] to [tblTimeOff], and [Employee] to [EmpID].

More important than meeting my particular preferences, however, is picking a standard that you can live with, and sticking to it.

Back to the is a query updateable issue.

Select 
   tTO.[ID], 
   tTO.[Employee], 
   tTO.[Date of Submission], 
   tTO.[Start Date], 
   tTO.[End Date], 
   tTO.[Status Code], 
   tEE.[Employee Name], 
   tEE.[File As] 
FROM 
   [Time Off] AS tTO, 
   [Employees Extended] AS tEE 
WHERE tEE.[ID] = tTO.[Employee];

This query is updateable as well. That is, the tTO fields are updateable. Since the two tEE fields are both calculated fields, which are based upon the [Employee] field from the tTO table ([Time Off]), they can't be directly updated.

Typically, if a form were going to be updating the Employee number for a Time Off record, using that query as a base, I would tend to use a dropdownbox bound to the Employee number, that only displayed the calculated name or File As name.

[swhennen85]

I added the query you mentioned and replaced the Time Off Extended. I've also renamed the tables, queries, and most of the fields, and attempted to redefine the relationships. Does this look any better?

[Dal Jeanis]

That was primarlily intended to be a tutorial with clear examples on what makes a query updateable or not...

I have a Dallas Access User's SIG this weekend. I'll look it over when I get the chance.