Query with same count returned to every record?

[scott0_1]

Hi All,



I have a fairly complicated query in Access I'm hoping to get some help for. Due to the nature of the DB, I will not be able to post any part of it here. I will try to put all relevant information in this post though.

The English version of what I'm trying to do: We have several districts with several offices. Each employee takes courses. The user will select a district and a course. From there, I am going to create a pivot chart bar graph to display offices on the district on the x-axis and number of employees on the y-axis. The bar for each office will be separated into three: Completed, not completed and not active (in case employees are away).

This will be drawn from the tables defined at the bottom of the post.

The fields I'll be looking for in the query result are:

tblDistrict.DistrictID, tblDistrict.DistrictName, tblOffice.OfficeID, tblOffice.OfficeName, tblCourseCatalogue.CourseID, tblCourseCatalogue.CourseName, tblEmployeesScheduled.Attended=True, Count of Employees at Office, Count of Inactive Employees at Office. Count of Employees at Office With the Selected Course, Count of Employees at Office Without Selected Course (=Total - With Course - Inactive).

Getting some of this info is very basic, but doing the counts is throwing me off. I've gone as far as making a DAO recordset and a two dimensional array. I'm then copying the office name into the first column of the array and using DCount for the next four columns, all in a For/Next loop. I'm not at work yet, so I haven't tried that code, but I'm sure it's going to be buggy. And it just seems unnecessary. I should be able to do this in the SQL query... Right?

Example query results in the table at the bottom of the post.

Thanks for any help you can offer!

Scott

DistrictID	DistrictName	OfficeID	OfficeName	CourseID	CourseName	TotalEmp	CountInactive	CountWithCourse	CountWithoutCourse
1	North	1	NorthVanA	1	FirstAid	10	1	7	2
1	North	2	NorthVanB	1	FirstAid	7	2	4	1

tblDistrict	tblOffice	tblEmployee	tblCourseCatalogue	tblEmployeesScheduled
DistrictID	OfficeID	EmpID	CourseID	CourseID
DistrictName	OfficeName	EmpName	CourseName	EmpID
		Active (yes/no)		Date
		OfficeID		Attended

[Gicu]

Your tblEmployee is missing OfficeID field, how can you know what office an employee works in? To get your data I would create separate totals (count) queries by linking tblEmployeesScheduled to tblEmployee and grouping by OfficeID and CourseID (equi join to get the ones that attended, left outer join to get the ones that didn't - having Is Null in the EmplID field from the tblEmployeesScheduled ), one totals query based on tblEmployee to get your inactive count and finally the last one that gets you the total number of employees by OfficeID. In this one to join all the other three (left outer join by OfficeID).

Cheers,
Vlad

[scott0_1]

Your tblEmployee is missing OfficeID field, how can you know what office an employee works in?

You're right, I forgot to type it in. I typed all of that this morning around 05:30 after being awake for two hours... Out of memory! Surprisingly, that's all I forgot!!! The original post is updated.

I just got out of a meeting about this DB, and my eyes can't keep straight, so I'm going for lunch, then I'll look over your suggestion.

Thanks for the help!

[scott0_1]

I can get the number of employees that have taken the course in one query, but if I try to get the count that hasn't, I get no records returned. I know it's the join I'm using, but I'm not sure how to write it. Right now I've got:THis is driving me crazy. I just threw a pen at my monitor. I think my co-worker next to me is scared.

Here's my statement that joins and shows all. I know there's redundant stuff in it. I'm just trying to work through it:
*tblEmployeeScheduled got changed from above to tblIndividualLearning

SELECT tblEmployee.EmpID, tblIndividualLearning.EmpID, tblIndividualLearning.CatelogID
FROM tblEmployee LEFT JOIN tblIndividualLearning ON tblEmployee.[EmpID] = tblIndividualLearning.[EmpID]
WHERE (((tblIndividualLearning.CatelogID)=19));

If I add "Is Null" to tblIndividualLearning.EmpID, I get nothing:

SELECT tblEmployee.EmpID, tblIndividualLearning.EmpID, tblIndividualLearning.CatelogID
FROM tblEmployee LEFT JOIN tblIndividualLearning ON tblEmployee.[EmpID] = tblIndividualLearning.[EmpID]
WHERE (((tblIndividualLearning.EmpID) Is Null) AND ((tblIndividualLearning.CatelogID)=19));

This gets me a grouped my office list of employees that have not had course #19. If I try to total by EmpID though, it just totals ALL EMPLOYEES, not just the ones in this list.

SELECT tblOffices.District, tblOffices.OfficeName, tblEmployee.EmpID
FROM tblOffices INNER JOIN tblEmployee ON tblOffices.ID = tblEmployee.Office
GROUP BY tblOffices.District, tblOffices.OfficeName, tblEmployee.EmpID
HAVING (((tblOffices.District)=54) AND ((tblEmployee.EmpID) Not In (SELECT tblIndividualLearning.EmpID
FROM tblEmployee LEFT JOIN tblIndividualLearning ON tblEmployee.[EmpID] = tblIndividualLearning.[EmpID]
WHERE (((tblIndividualLearning.CatelogID)=19))
)));

[Gicu]

Hi Scott,

Have a look at the attached file, look at the query qryCOUNTS and see if that is what you're after.

Cheers,
Vlad

[scott0_1]

Vlad, thanks for the file, but unfortunately my company has our computers so tightly clamped down, I can't download it and open it. I'm lucky to be able to use Access and VBA!!!

I did find a solution to my problem though. I used part of your first suggestion with multiple queries. I used one to total the number of employees, active, inactive at each location then I queried against that by course and just did some math inside the SQL statement (I didn't know that it was that easy to do, SQL is not my forte!).

Thanks for the help!!!

[Gicu]

Great to hear, basically I did the same (calculated the ones that did not take the course as the total employees minus the ones that did take it).

Cheers,
Vlad