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
> Training Course Completed Summary, Access 2016    
 
   
Felixx_Bodhran
post Feb 21 2018, 11:21 AM
Post#1



Posts: 38
Joined: 12-February 18
From: UK


Hi,

I hope someone can help, I have started creating a training database and have setup the tables as below:

tblEmployeeInfo
FirstName
LastName
StartDate
DepartmentFKID
CentreFKID

tblDepartments
Department

tblCourses
Course

tblOffices
Office

jtblCourseEmployee
CourseFKID
EmployeeFKID
DueDate

I have created queries and forms that display the employee's:

Name, StartDate, Department, Office, and Course Name

under each of the course names for each employee it shows their due date for the course to next be completed by

Please can I have your help/suggestions (because I cannot work out how or think of the best way to do it) with creating a query or report that shows at a glance a summary showing the:

Office Name
Department Each Course Name Percentage of people who have complete each course

I hope that made sense. Thank you
Go to the top of the page
 
doctor9
post Feb 21 2018, 11:29 AM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Felixx,

How do you define "completed" using the fields you've listed? It's one thing to have a due date, but that seems more like a deadline or target rather than an indication that the course was actually completed. You may want to add another field to jtblCourseEmployee, like a completion date or a yes/no field for whether the course was completed or not. Once you have a clear definition of whether the course was completed or not, the query should be fairly straightforward.

By the way, is CentreFKID linking to the tblOffices table? If so, you may want to use more consistent naming, like "OfficeFKID" instead of "CentreFKID", and "tblEmployees" instead of "tblEmployeeInfo", just to make maintenance easier. Also, do your tables have primary key fields that you didn't list? I'm assuming they do.

Hope this helps,

Dennis
Go to the top of the page
 
Felixx_Bodhran
post Feb 21 2018, 11:44 AM
Post#3



Posts: 38
Joined: 12-February 18
From: UK


Hi Dennis

Thank you for such a quick reply. The Due Date is actually a deadline as you state. A completed yes or no sounds like a great idea, I will add that in. But I'm not familiar with access enough to generate the Summary I'm looking for.

I made an error there, I meant OfficesFKID not CentreFKID. Your suggested naming makes more sense for Employees I'll change that. All my tables do have primary key fields that are autonumbers but I haven't changed the name to anything different than ID, should I be?

Thanks
Go to the top of the page
 
doctor9
post Feb 21 2018, 11:54 AM
Post#4


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Felixx,

For primary key fields, I personally use the name of the table and add "ID". That way when you see the field in a query or in code, you know immediately which table it's a primary key for. So, for tblEmployees, the primary key field would be "EmployeeID", for tblOffices, it would be "OfficeID".

I'll see if I can put together a basic query for you based on your tables.

Dennis
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 12:31 PM
Post#5


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


PMFJI;

Actually there are two schools of thought regarding the naming of PK fields. One very highly respected developer leaves them as "ID", I understand. The theory is that if you need to disambiguate in a specific context, you can do so by including the table: tblDepartments.ID vs tblEmployeeInfo.ID

I think the majority of developers do, in fact, follow the same practice Dennis outlines.

Like so many things in Access, there is no one "right" or "wrong" way. Just what seems more intuitive to the developer.
Go to the top of the page
 
MadPiet
post Feb 21 2018, 01:11 PM
Post#6



Posts: 2,606
Joined: 27-February 09



Felix,

How do you determine which employees need to take which courses? Is it by department, by job type?
I have built a bunch of these - my two cents is to focus on the tables and queries first. Worry about the forms and reports later.

Pieter
Go to the top of the page
 
doctor9
post Feb 21 2018, 02:38 PM
Post#7


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Felixx,

Okay, I'm putting something together, but I've noticed something vague about your request.

Here's what I think you're looking for:
Office Name, Department Name, Course Name, Percentage of people who have complete each course

But you're trying to SUMMARIZE how many people have taken a given course. Why is the Office and Department included? Do you want to see the percentage of people from the Accounts Receivable department in the Corporate HQ building completed the Fire Safety course? And then see a separate record for the Marketing department in the Corporate HQ building completed the Fire Safety course? And another record if you have another Accounts Receivable department in the Shipping building?

Just trying to clarify what the "Percentage of people who have completed each course" field is showing. Do you want one record for each unique course? Or are you trying to see a breakdown of each course's students by department AND building AND course?

Hope this helps,

Dennis

EDIT: Assuming you want the breakdown, I think you can do this with three queries. One to count the TAKEN courses, broken down by the three categories, one to count the COMPLETED courses, broken down by the three categories, and a third one to join the first two queries and divide the completed number by the taken number.

qryTakenCourses:
SELECT tblEmployees.DepartmentFKID, tblEmployees.OfficeFKID, tblTakenCourses.CourseFKID, Count(tblTakenCourses.TakenCourseID) AS CountOfTakenCourses
FROM tblEmployees INNER JOIN tblTakenCourses ON tblEmployees.EmployeeID = tblTakenCourses.EmployeeFKID
GROUP BY tblEmployees.DepartmentFKID, tblEmployees.OfficeFKID, tblTakenCourses.CourseFKID;

qryCompletedCourses:
SELECT tblEmployees.DepartmentFKID, tblEmployees.OfficeFKID, tblTakenCourses.CourseFKID, Count(tblTakenCourses.TakenCourseID) AS CountOfCompletedCourses
FROM tblEmployees INNER JOIN tblTakenCourses ON tblEmployees.EmployeeID = tblTakenCourses.EmployeeFKID
WHERE (((tblTakenCourses.boolCompleted)=True))
GROUP BY tblEmployees.DepartmentFKID, tblEmployees.OfficeFKID, tblTakenCourses.CourseFKID;

qryBreakdown:
SELECT tblOffices.Office, tblDepartments.Department, tblCourses.Course, qryTakenCourses.CountOfTakenCourses, qryCompletedCourses.CountOfCompletedCourses,
[CountOfTakenCourses]/[CountOfCompletedCourses] AS PercentageComplete
FROM (((qryCompletedCourses
INNER JOIN qryTakenCourses ON (qryCompletedCourses.DepartmentFKID = qryTakenCourses.DepartmentFKID)
AND (qryCompletedCourses.OfficeFKID = qryTakenCourses.OfficeFKID)
AND (qryCompletedCourses.CourseFKID = qryTakenCourses.CourseFKID))
INNER JOIN tblOffices ON qryTakenCourses.OfficeFKID = tblOffices.OfficeID)
INNER JOIN tblCourses ON qryTakenCourses.CourseFKID = tblCourses.CourseID)
INNER JOIN tblDepartments ON qryTakenCourses.DepartmentFKID = tblDepartments.DepartmentID;

(These will look pretty simple in Design View.)

This isn't thoroughly bug tested, but it makes sense in my head. Notice how the foreign/primary key field names match up? That's why I recommended naming the primary key after the table.

Hope this helps,

Dennis
Go to the top of the page
 
Felixx_Bodhran
post Feb 22 2018, 05:04 AM
Post#8



Posts: 38
Joined: 12-February 18
From: UK


Dennis, thank you so much for this, its perfect, it is exactly the information I needed to get and I now understand the importance of queries and naming conventions.

Pieter, the courses are allocated based on department

Again I can't thank you guys enough, this is my first time using Access and this forum is such a great resource.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th December 2018 - 12:48 AM