UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Simple Training Database, Access 2010    
 
   
scewing
post Dec 14 2017, 07:26 AM
Post#1



Posts: 33
Joined: 2-June 17



I've searched here and internet-wide but can't find a solution to my problem. I need to track employee training, but not as in detail as I see in some solutions here. But more importantly, I need to track training points.

To be "qualified" for a job, an employee needs to maintain 5 points. Each course is worth a varying amount of points (i.e. Course1 worth 2 pts., Course2 worth 3 pts., Course3 worth 1 pt., etc.). All points expire after 5 years.

I have tblEmployees and tblCourses, and tblEmployeeCourses records the date a course is taken for each employee.

My problem is I need to make a summary query (I think) that will show each employee current points and (probably most importantly) when the next points expire (what new point total will be). I'm also not sure of how to actually make points expire (I think a SumIf statement using course date + 5 years is the way to go, but not sure how/where to implement). Anyone have any ideas for me?
Go to the top of the page
 
ScottGem
post Dec 14 2017, 07:54 AM
Post#2


UtterAccess VIP / UA Clown
Posts: 32,188
Joined: 21-January 04
From: LI, NY


Your tblCourse should have field for points. So a simply Group By query should do the job.

You want to include the tblEmployeecourses and tblCourses, joined on CourseID. You then add the EmployeeID column and the Points column and Date column. Turn the query into a Group By which will add a Group By row. In that row set the EmployeeID to Group By. The Points column to Sum. And the Date column to Where. In the Criteria for the Date column use:
>DateAdd("yyyy",-5,Date())

This query will show the total points for each employee during the last 5 years.

You can then do a report that shows those employees with less than 5 points. Or those employees with expiring points.

You can create another report that doesn't sum the points but will be sorted by Date in Descending order. That query will tell you what points will expire soon. You can then use that as the basis for a report, that groups by employee and sums the points.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
GroverParkGeorge
post Dec 14 2017, 08:38 AM
Post#3


UA Admin
Posts: 31,654
Joined: 20-June 02
From: Newcastle, WA




I believe Scott's outlined approach is the way I would go about it, but one thing that would help us offer more detailed suggestions would be to actually see the tables involved.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 14 2017, 08:53 AM
Post#4


UA Admin
Posts: 31,654
Joined: 20-June 02
From: Newcastle, WA


There is a potential complication in the way points for courses are allocated. Is each course always going to be worth the same number of points? Or, is there the possibility that the points could be changed from time to time as the courses change, or the skills taught in those courses become more or less important, and so on. And, if that happens, how do you handle those changes for existing records?


--------------------
Go to the top of the page
 
projecttoday
post Dec 14 2017, 10:25 AM
Post#5


UtterAccess VIP
Posts: 8,823
Joined: 10-February 04
From: South Charleston, WV


I would suggest making either a form or a report which shows the details as well as the points total so that you will be able to show the student a breakdown of what points he/she has.

--------------------
Robert Crouser

Go to the top of the page
 
scewing
post Dec 14 2017, 11:25 AM
Post#6



Posts: 33
Joined: 2-June 17



Thanks Scott,

That approach works. There seems to be something off about the Where statement: without it, I get a point total; with it, I just get blank results. I'll keep playing with it to get it right.

Grover,
I think if the point values change, they would have to make a major change to the course which would result in a new course number. I believe they would also have to honor the point value for the old course until the points expired after the standard 5 year period, so I think I'm covered there.

projecttoday,

The person responsible for monitoring this training "situation" is an older-generation, "everything paper" kind of person, so I plan on going the report route. I plan to generate a report that will show each person's point totals with color coding >5-green, 5-orange, <5-red kind of thing, with maybe a date next course expires field, or something.

Thanks everyone for the quick and helpful responses.
Go to the top of the page
 
GroverParkGeorge
post Dec 14 2017, 11:36 AM
Post#7


UA Admin
Posts: 31,654
Joined: 20-June 02
From: Newcastle, WA


I've come to believe that assumptions about how things are, have been, and always will be tend to be more fluid than we'd like to think. smirk.gif

Best of luck with your project.

George


.

--------------------
Go to the top of the page
 
projecttoday
post Dec 14 2017, 06:33 PM
Post#8


UtterAccess VIP
Posts: 8,823
Joined: 10-February 04
From: South Charleston, WV


You can certainly show the totals AND the details in a (paper) report. Just create a report of the details and then put a group in it for the employees. Put the points total in the employees footer.

--------------------
Robert Crouser

Go to the top of the page
 
ScottGem
post Dec 15 2017, 06:18 AM
Post#9


UtterAccess VIP / UA Clown
Posts: 32,188
Joined: 21-January 04
From: LI, NY


That criteria should give you any records within the last 5 years. Can you post your SQL?

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
scewing
post Dec 18 2017, 01:17 PM
Post#10



Posts: 33
Joined: 2-June 17



Sorry, Scott. The code was perfect. I was in command line mode in my head and ignored the ">" at the beginning of the expression.

Thanks again!
Go to the top of the page
 
scewing
post Dec 18 2017, 02:10 PM
Post#11



Posts: 33
Joined: 2-June 17



Scott,

I've added a calculated field to that original query you suggested, that shows when the next points (course) will be expiring fairly easy...
NextPointExp: DateAdd("yyyy",5,[dteDateCompleted]) and then set the Total row to "Min." This works perfectly.

Really the only thing left I need is to show what the points would be when that day comes and those particular points expire. I'm using the points field summed as before, but I'm trying to specify criteria and not having any luck getting the right expression.
I'm trying to simulate that (Date() = NextPointExp +1 day) but of course that doesn't work. Maybe I need a different query? Any ideas?
Go to the top of the page
 
ScottGem
post Dec 23 2017, 10:07 AM
Post#12


UtterAccess VIP / UA Clown
Posts: 32,188
Joined: 21-January 04
From: LI, NY


Use DateAdd to get the day after but I would do it in a separate query

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2018 - 07:05 PM