Attendance database help

[jzwp22]

If I remember correctly, you said that an employee can have more than 1 active job description, if so, then you cannot include the employee job description table in your summing query. Additionally, since you already have the points in the attendance type table there is no need for this:

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

You would just use a query that joins the attendance table and the attendance type table

SELECT tblEmployeeAttendance.fkEmployeeID, tblEmployeeAttendance.dteAttendance, tblAttendanceTypes.longPoints
FROM tblAttendanceTypes INNER JOIN tblEmployeeAttendance ON tblAttendanceTypes.pkAttendanceTypeID = tblEmployeeAttendance.fkAttendanceTypeID;


You would use the above query as the base for your summing query.


Back to the job descriptions, if each person only had 1 active job that could be handled with an aggregate query that finds the most active job based on a date field (you would have to add one to your table) using the MAX(datefield), but since, in your case, a person can have multiple active jobs that has to be handled differently. What you have to do is concatenate the jobs from multiple records into 1. Fortunately, others have developed a custom function to handle that; here is a link to a site with that function.

 

[Coleman984]

Ok I will review this and see if I can finally get these last two reports working. Thanks so much.

 

[Coleman984]

Absolutely brilliant, that was the only thing that I did wrong, was have the table in the query with the job descriptions, this was causing the employees points to be multiplied by their jobs.

 

[Coleman984]

Jz I don't know if you still have the sample database you made for me. But I'm having trouble with a query using employeejobs. the table employeejobs has a sub table with employee names that have said jobs. Not sure if I'm explaining this correctly. Anyway what I want is 1) a query with a count of how many persons have each job. 2) a query that uses the one you helped create previously that shows persons per department. Only this new query should show persons per job and department. Both are useful for different reasons. Meaning if there are 40 order selectors scheduled on any day then I want to know the break down for them, 10 Deli, 30 produce or whatever the numbers are. Does that make sense?

Every time I try to make a query of employeejobs I can't get anything intelligible out of it.

 

[Coleman984]

Data access page.

How would I go about creating a data access page instead of a form while still retaining all the functions the form has on it? Is this possible? Why wouldn't I want to do this? or why is this a good idea?

 

[jzwp22]

Regarding the query, since a person can have many jobs concurrently, you have to deal with that part first. When you do your count do you only want a specific job titles such as "order selectors" as you mentioned? You will have to create a query that only selects that particular job title and then use that query in another query that links back to the person/department. From there you should be able to do your count.

I've never worked with data access pages and they are not supported in Access 2007/2010; integration with Sharepoint is the newer approach

 

[Coleman984]

Subform in report

I'm trying to make the report I've created that shows an employees name , ID Number and points, more informative by adding the data from frmEmployeeAttendance to it as a sub form. However when I do this it is causing the employee (which is selected from the active employee when the report is opened from frmEmployee) to be listed for each employee with attendance marks. What I mean by this is the subform will show once correctly, then the first employee name will be exchanged for some other persons name creating over 30 pages of false and unwanted data.

How do I create a form that shows information from the following query:

Individual Employee Points

And has the subform frmEmployeeAttendance with information pertaining to the employee that was the active record on frmEmployee when the command button was clicked?

Without the subform, the Information pertaining to name and total points functions correctly. Add in the subform and the data is all wonky. I don't get it.

 

[Coleman984]

I took a step back and decided on a different approach. I made a query that shows the points for an attendance mark, date of attendance mark, the text value for that attendance type, and fkEmployeeID to get the information needed. Worked pretty well. Not the way I tried it at first but it looks nice and did the job. thanks for the help up to this point that allowed me to use things I've learned to solve this.

 

[jzwp22]

You're welcome!