Attendance database help

[Coleman984]

How would I get information about out of a sub form (this sub form is a query). Stores into a variable (really if I can store it into a variable I can do whatever it is that would need to be done with it).

The form name is AttendanceEntry:
The subform query name is EmpInfo

 

[jzwp22]

There were some issues with your structure. You were linking via the employee ID number rather than the primary key of the employee table. Although this would be OK, generally it is recommended that the primary key of a table have no significance to the user. Also, you want to enforce referential integrity for your relationships. I have made the necessary changes to the structure, the relationships and adjusted the data accordingly. I also created a form (frmEmployee) with several subforms that illustrates a typical way of presenting the data. If you enter data in the forms or subforms, it will be automatically stored in the underlying tables. The DB is attached. Note, I have Access 2010, so you might get an error message when you try to execute code. You will need to clear the MISSING reference to Access 2010 (Access 14) in the VBA window-->Tools-->References (uncheck anything that says MISSING).

Now as to the append query, what exactly do you want to append and to where?

Under your business rules, do you record the presence or absence of each employee for each day they are suppose to work or each day no matter whether they are supposed to work or not? I would think that it would be easier to just record when a person who is suppose to work is absent, but that might not be consistent with your rules.

Now if you want to add records to the attendance table for each day an employee is suppose to be off, you can do this but it will take some code (and will use an append query in that code). You can set it up to do it at the beginning of the year and loop through each employee and add their days off to the attendance table for the entire year.

 

[Coleman984]

Well I looked at the database and it looks great. It appears to be functional minus any other reports / queries I would want the person entering the data to have access to.

One thing is how would I get the days off to display with the employee info? Like below Department, without removing the tab to change the days off.
Also would it be possible to have a drop down box for the first name (showing first and last in the listbox similar to the way it shows the attendance type and point value). This would allow a user to jump from one record to another. How would this be done? I started to mess around with something like that on the version of the database and had problems with it mostly because I have no idea what I was doing.

Now as to the append query, what exactly do you want to append and to where?

I'm not sure you mentioned this in one of your previous posts that I should do this. Not sure what you had meant with this.

Under your business rules, do you record the presence or absence of each employee for each day they are suppose to work or each day no matter whether they are supposed to work or not? I would think that it would be easier to just record when a person who is suppose to work is absent, but that might not be consistent with your rules.

Not sure how to answer this question, so I think it may be easier if I just said what the end result would be? The end result will be; A query will be available to show how many points a employee has within the year. And the reason for marking when an employee is off is so the person entering the data will know whether or not to mark the employee ABS. The threshold for a filter would be stored in a listbox or something. So the person using the form / query would be able to change which amount of points to be shown. Increments of 1, starting at one.
I'm not sure how best to track this, really a separate sub-form or what not showing the days off of the employee would suffice so the person entering the data can choose Off when selecting what attendance mark to give the employee.
Did that answer the question? I'm sorry if it did not and I will try again to clear it up were needed.

Now if you want to add records to the attendance table for each day an employee is suppose to be off, you can do this but it will take some code (and will use an append query in that code). You can set it up to do it at the beginning of the year and loop through each employee and add their days off to the attendance table for the entire year.

back attendance will not be inputted into this database, only going forward. so I think the answer to this would be no.

And thanks again for your continued help.

 

[Coleman984]

What would be the best way to move an employee to inactive? IE they are fired? And how would this be done? If an employee were to be "fired" how would that be done as far as user forms goes. If this isn't to much to ask. I'm going to try to make a form that will add an employee.

How would the names be sorted on the frmEmployee such that they are arranged alphabetically?

 

[jzwp22]

One thing is how would I get the days off to display with the employee info? Like below Department, without removing the tab to change the days off.

I'm not sure I follow. Since a person can have more than 1 day off, those days off have to be shown in a subform. You can move the subform from the tabbed control to somewhere else on the main form.

Also would it be possible to have a drop down box for the first name (showing first and last in the listbox similar to the way it shows the attendance type and point value).

If you do this in the body of the main form you would end up changing the name of the person in the database for the current record since those controls on the form are bound to the underlying table. This would mess up your data. However, you could put an unbound control on the form (in the header) and when selected Access could move to that record. I've added that to the attached DB

A query will be available to show how many points a employee has within the year.

This can be done. I will let you try to work out the query since I really do not want to design your whole database. Hint: create a query and include the attendance table and the attendance type table. Then select the fields you want. Save the query. Create another new query and select the query you just selected. Right click a column and select Totals and then sum the points field.

The threshold for a filter would be stored in a listbox or something. So the person using the form / query would be able to change which amount of points to be shown. Increments of 1, starting at one.

I'm not following what you are saying here.

What would be the best way to move an employee to inactive? IE they are fired? And how would this be done? If an employee were to be "fired" how would that be done as far as user forms goes. If this isn't to much to ask. I'm going to try to make a form that will add an employee.

This is typically done with a field in the employee table. I typically use a date field. You would put a date in the field corresponding to when the employee became inactive. You can then use a query to filter on that field so you do not see inactive employees. (Select all records where the date field is Null).

How would the names be sorted on the frmEmployee such that they are arranged alphabetically?

You can accomplish this with a query as well.

I have modified the employee form in the attached database to use a query based on the employee table rather than using the employee table directly. The query ignores records where there is a date in the field I suggested as well as orders the employees by last name and then first name

If this isn't to much to ask. I'm going to try to make a form that will add an employee.

No need, you can use the same form I created to edit existing records or add new records. You can either navigate to the last record and then go one further and you will be presented with a blank form to enter or a new record, or you can click on the asterisk button next to the record navigation buttons (in the lower frame of the main form).

 

[Coleman984]

Thanks for your help. I'm no longer feeling like this is pointless (mainly because you have done most of the hard work heh).

I'm not sure I follow. Since a person can have more than 1 day off, those days off have to be shown in a subform. You can move the subform from the tabbed control to somewhere else on the main form.

Actually since I no longer intend to track days employees are present (because I agree with your logic, the point of this database is to know when someone isn't here and why, not when they are here).

therefore this part is no longer relevant.

This can be done. I will let you try to work out the query since I really do not want to design your whole database. Hint: create a query and include the attendance table and the attendance type table. Then select the fields you want. Save the query. Create another new query and select the query you just selected. Right click a column and select Totals and then sum the points field.

I do have a total points query working now. Then i've created a form and I want to be able to filter the subform i've created. this part i'm stuck on. I want the user to be able to choose which records are shown, based on the points entered into a text box. Everything I find is assuming that the forms are parent child, which would be fine except I don't know how to create that sort of relationship and can't find a how to on it.

This is typically done with a field in the employee table. I typically use a date field. You would put a date in the field corresponding to when the employee became inactive. You can then use a query to filter on that field so you do not see inactive employees. (Select all records where the date field is Null).

I am having trouble with filters on forms. But once I understood that I'd imagine I could do this.

I need another filter I just realized. A toggle to show or hide inactive employees on frmEmployees. I am going to add a text box for inactive date.

do you think you could explain to me how to do a filter that is less than strait forward? Let me have a go at it, then perhaps after a day or two of trying if I can't get the filters working the way I want them to. Help me by showing the solution to me?

 

[jzwp22]

I am having trouble with filters on forms. But once I understood that I'd imagine I could do this.

I need another filter I just realized. A toggle to show or hide inactive employees on frmEmployees. I am going to add a text box for inactive date.

do you think you could explain to me how to do a filter that is less than strait forward? Let me have a go at it, then perhaps after a day or two of trying if I can't get the filters working the way I want them to. Help me by showing the solution to me?

I adjusted the query behind the main form so that it will ignore inactive employees, you just have to put a date in the field. I did forget to put the corresponding date control on the form. I'll leave that to you. As to filters, I generally do not use them at least not directly. I typically use them in code when I need them.

I do have a total points query working now. Then i've created a form and I want to be able to filter the subform i've created. this part i'm stuck on. I want the user to be able to choose which records are shown, based on the points entered into a text box. Everything I find is assuming that the forms are parent child, which would be fine except I don't know how to create that sort of relationship and can't find a how to on it.

Can you provide an example of what/how you want to display the points?

 

[Coleman984]

I adjusted the query behind the main form so that it will ignore inactive employees, you just have to put a date in the field. I did forget to put the corresponding date control on the form. I'll leave that to you. As to filters, I generally do not use them at least not directly. I typically use them in code when I need them.

Thats what I meant actually, to use code filters. Hiding inactive employees is good, but would there be code that could be assigned to a toggle that would show inactive employees? So they could then be reactivated if needed.

Can you provide an example of what/how you want to display the points?

UPDATE!
This part is partially solved right now. The only thing I need to be able to do is make a new form Which will generate a report with persons with points higher than the number entered in the form.

Post before EDIT!
The form i've made contains a subform which is a query which shows the employees that have points greater than 0 (the criteria set when the query was made). However on said form I want a text box that a number can be entered into that when a number was entered it would show only employees with points equal to or greater than the number. Example employee1 has 1 point, employee2 has 3 points, employee3 has 5 points. I enter in 4 into the textbox and employee2 and employee1 are then hidden.

 

[Coleman984]

Actually you know what I have added another page to the frmEmployee and made a new form called frmPoints. Added that form to the frmEmployee, finally got a dlookup working now that I have the syntax correct. To lookup the employee name from empid, and to lookup the attendancetypeID, from there created an iif statment to return the point value.
The biggest thing that is bugging me is a couple of the textbox formulas are showing #Error when it can't find anything to return. Now in excel this would be easy to get the value returned that I want on an error. In excel 2003 it would be a simple if(iserror("test for error","return error value","no error do formula again") in excel 2007 even easier but since some users are still using 2003 I tend to use the 2003 solutions when possible. Anywho, how do I do a if(iserror( in access text boxes?
Saw how you did the record source stuff with the other tabs. Got the new form linking up the way they are supposed to.

Update
I'm trying to get a combo box to show the dates of the active employee that they were absent or what not. This combo will ulitimately be 3 columns. 1st Columning showing the date, the 2nd what they were marked, and the 3rd how many points that mark got them.

I was trying to get the right dates to show in the combo box. But it shows all dates in the table and not the active employees. I know that this statement would not show the other two columns I want but I am trying to do only 1 at a time.

SELECT tblEmployeeAttendance.dteAttendance FROM tblEmployeeAttendance WHERE (((tblEmployeeAttendance.fkEmployeeID)=[txtEmpID]));

 

[jzwp22]

IsError() is a valid function within Access, so you should be able to nest that with the IIF() function as necessary.

SELECT tblEmployeeAttendance.dteAttendance FROM tblEmployeeAttendance WHERE (((tblEmployeeAttendance.fkEmployeeID)=[txtEmpID]))

If txtEmpID is another control on the form, you have to use the full form reference forms!formname!txtEmpID. If the control is on a subform then the reference is a little trickier. This site shows how to reference various controls and properties on forms/subforms.

...not the active employees

It would be best to create a query that utilizes both the attendance table and the employee table (and filter based on the date field we discussed earlier). Use that query for the row source of the combo box.

 

[Coleman984]

IsError() is supported? No matter how I wrap the formula it still gives an error. The textboxes in question are the days off name text box and the one I created. For the name for the employee on the newly created Attendance Marks tab.

 

[Coleman984]

How can I toggle the filters to show / hide inactive employees (with vba code).

Would a report based on the employees points be created using a query?

How can I modify a queries parameters with vba code when a command button is clicked?

thanks so much for the help you've given so far as always.

 

[jzwp22]

IsError() is supported? No matter how I wrap the formula it still gives an error. The textboxes in question are the days off name text box and the one I created. For the name for the employee on the newly created Attendance Marks tab.

Perhaps this site might help. Without knowing exactly where you are trying to do this, it is hard to diagnose. Can you post your database as it currently stands?

How can I toggle the filters to show / hide inactive employees (with vba code).

This http://allenbrowne.com/ser-28.html should give you the basics even though it is geared more for a subform.

How can I modify a queries parameters with vba code when a command button is clicked?

There are a variety of ways to do this. You can build the query directly in code and assign it to a form's record soruce (I use this method quite often) or you can modify an already saved query, or you can use the parameters themselves. You will want to check out Roger Carlson's site. He has example databases for all of his tips.

Would a report based on the employees points be created using a query?

Yes, since you need at a minimum both the attendance table as well as the attendance type table. If you want to show the employee name as well, then of course you will need the employee table in the query as well.

 

[Coleman984]

Thanks for all the help on this. I will look into those sites and try to learn the rest of what I need. Truly thankful for all the time and effort you have put into helping me on my way.

 

[jzwp22]

You're welcome; best of luck as you move forward.

 

[Coleman984]

jz I have no idea what I did but on the jobs tab it says this whenever I try to add a job for someone:

Control can't be edited; it's bound to AutoNumber field 'pkEmpJobDescID'. How do I fix this?

 

[Coleman984]

Ok i guess I had the wrong control source. Should have been fkJobDescID but I had it set to the wrong field. got it, just panicked heh.

 

[jzwp22]

Glad you got it sorted out.

 

[Coleman984]

hey jz not sure if you read my post where I was noticing some employees were having there attendance points multiplied. Well I just discovered what number it was being multiplied by, the number of jobs the employee has. Now how to fix this at the root cause, not sure. But if you could help me modify this iif formula such that it would divide the formula already in place by the number of jobs the employee has. I bet that would be good enough.

would I need to create a new query with a job count? I think that is it. I will try that and if it works I'll post again.

the tables in this query are as follows:

tblEmployee
tblEmployeeAttendance
tblEmployeeJobDescriptions



Sum(IIf([fkAttendancetypeID]=2,1,IIf([fkAttendancetypeID]=3,0.5,IIf([fkAttendancetypeID]=4,0.5,IIf([fkAttendancetypeID]=5,1,0)))))

 

[Coleman984]

Ok so I created a query that shows how many jobs an employee has. but when I added a / qryempjobcount.empjobcount at the end of my existing formula the results went crazy...