Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function

[fmcconaughy]

Hello all, I have a problem trying to use an aggregate function to calculate values from a separate query. I have two separate queries, “Employees extended” and “DeliverablesPropNo”

“Employees” Relevant headers:

“Employee ID”, “Employee Name”



“Deliverables” relevant Headers:

“Job Name”, “Submitted By”, “Status”

Notes:

-Submitted by is displayed as a string but is a drop down of type Number referencing the employee ID in employees extended

- Status is also a drop down of type String

-There are about 50 employees, and about 700 rows in "DeliverablesPropNo”

In theory what I would like to do in a query is for each employee (each as a row) (called out as “Submitted By” in “Deliverables”) search through “deliverables” and create a count of all jobs of each status associated with that employee.

Ideally, the query values should be as follows, with a row for each employee:

“Employee Name”, “# of status 1 assigned to employee”, “# of Status 2 assigned to employee”….

When I try to do this in the query builder, I keep getting the error “Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function”. I am not quite sure what to do here as I am a bit inexperienced with SQL, and help would be appreciated. Thank you!

Count syntax:

Countstatus1: Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 1",1,0))

My SQL Code is Below:

SELECT [Employees Extended].ID, [Employees Extended].[Employee Name], Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 1",1,0)) AS countstatus1, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 2",1,0)) AS countstatus2, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 3",1,0)) AS countstatus3, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 4",1,0)) AS countstatus 4, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 5",1,0)) AS countstatus5, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 6",1,0)) AS countstatus 6, [countstatus1]+[countstatus2]+[countstauts3]+[countstatus4]+[countstatus5]+[countstatus6] AS tot

FROM DeliverablesPropNo, [Employees Extended];

Please let me know is anything is unclear or if you need more information! Thanks!

 

[plog]

When you create an aggregate query (SUM, COUNT, MAX, MIN, etc.) then every field that is in the SELECT must either be in an aggregate function (SUM, COUNT, MAX, MIN, etc.) or be in the GROUP BY.

You have no GROUP BY. So, you either need to get rid of [Employees Extended].ID, [Employees Extended].[Employee Name] in the SELECT or add a GROUP BY with them in it:

GROUP BY [Employees [Employees Extended].ID, [Employees Extended].[Employee Name]

However, I think your query is fundamentally wrong. Normally, a query is FROM one datasource and then others are joined to it. You have 2 data sources in your FROM. Why is that? I mean, these tables are related as demonstrated in your IIF statement:

...Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID]...

So why not JOIN them in the FROM instead of manually in all those fields?

I also question having each status as a field name (horizontally). Why not just include each status as data in a record (vertically)?

What is the purpose of this query? How is it being used? I think there's a better way to achieve whatever the ultimate end goal is.

 

[fmcconaughy]

When you create an aggregate query (SUM, COUNT, MAX, MIN, etc.) then every field that is in the SELECT must either be in an aggregate function (SUM, COUNT, MAX, MIN, etc.) or be in the GROUP BY.

You have no GROUP BY. So, you either need to get rid of [Employees Extended].ID, [Employees Extended].[Employee Name] in the SELECT or add a GROUP BY with them in it:

GROUP BY [Employees [Employees Extended].ID, [Employees Extended].[Employee Name]

However, I think your query is fundamentally wrong. Normally, a query is FROM one datasource and then others are joined to it. You have 2 data sources in your FROM. Why is that? I mean, these tables are related as demonstrated in your IIF statement:

...Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID]...

So why not JOIN them in the FROM instead of manually in all those fields?

I also question having each status as a field name (horizontally). Why not just include each status as data in a record (vertically)?

What is the purpose of this query? How is it being used? I think there's a better way to achieve whatever the ultimate end goal is.

Thank you!
I will try grouping and see if that helps!
to your second point, I am not sure how I can join these two queries as they each have different data in them, though I may be misunderstanding what you mean by join in this context.
To answer your last question, the status is listed horizontally as the intent of this query is to list all employees, and how many jobs of each status they have assigned to them.
For example;
Headers:
Employee, pending jobs, budgets jobs, complete jobs
Data:
Jon Doe, 3, 4, 2
Jane Doe, 2, 5, 6

so from this data I can tell that Jon has 3 pending jobs, and 2 complete jobs, while Jane has 2 pending and 6 complete.

wheras in DeliverablesPropNo

Headers:
Job, Employee, Status
Data:
job1, jon doe, pending
job2, jane doe, budget
job3, jon doe, completed

please let me know if this clears anything up,
Thanks again

 

[June7]

Sample data could be helpful. Build tables in post or attach file (instructions at bottom of my post).

Query with multiple tables and no JOIN clauses causes a Cartesian association of records - all records from each table associate with all records of other table(s), greatly multiplying the quantity of records returned. If table A has 5 records and table B has 3, the number of records returned will be 15.

If data is normalized, as seems to be from the schema shown, a CROSSTAB should be able to produce pivoted output. Multiple SUM() expressions appears to be emulating a CROSSTAB.

Why doesn't that data sample show EmployeeID key field in Employees and this saved as foreign key in Deliverables? Names are very poor unique identifiers and repeating text is poor design.

Advise not to use spaces in naming convention.

 

[fmcconaughy]

I will work on a slimmed down database, though in the meantime I am not quite sure what you mean by "Why isn't there EmployeeID key field in Employees and this saved as foreign key in Deliverables?" if it helps, the drop down for employee in deliverables calls the employee ID and employee name, and only displays employee name.

I'm sorry if I seem ignorant, I have only been working with access for a few months and am basically self-taught by trying to decipher how the last guy built the database, and falling back on google when I can't figure it out.

 

[June7]

Your query shows ID fields but sample data you posted does not.

And the numbers shown for status categories don't reconcile with the Deliverable data. I built tables and added records to agree with numbers given.
Consider this SQL:

TRANSFORM Count(Deliverables.JobID) AS JobCount
SELECT Employees.EmployeeID, Employees.EmployeeName, Count(Deliverables.[JobID]) AS [TotalCount]
FROM Employees LEFT JOIN Deliverables ON Employees.EmployeeID = Deliverables.EmpID
GROUP BY Employees.EmployeeID, Employees.EmployeeName
PIVOT Deliverables.Status IN("budget","pending","completed");

EmployeeID	EmployeeName	TotalCount	Budget	Pending	Completed
1	Jon	9	4	3	2
2	Jane	13	5	2	6
3	Kelly	0

To learn more about CROSSTAB query, start with http://allenbrowne.com/ser-67.html

 

[fmcconaughy]

Your query shows ID fields but sample data you posted does not.

And the numbers shown for status categories don't reconcile with the Deliverable data. I built tables and added records to agree with numbers given.
Consider this SQL:

TRANSFORM Count(Deliverables.JobID) AS CountOfJobID
SELECT Employees.EmployeeID, Employees.EmployeeName
FROM Employees INNER JOIN Deliverables ON Employees.EmployeeID = Deliverables.EmpID
GROUP BY Employees.EmployeeID, Employees.EmployeeName
PIVOT Deliverables.Status IN("budget","pending","completed");

EmployeeID	EmployeeName	Budget	Pending	Completed
1	Jon	4	3	2
2	Jane	5	2	6

​

Grouping seemed to get things to work! I will post updated cleansed SQL below, and will also post an attachment with a cleansed database for clarity.
To answer your question, the numbers are derived by the following, for say jon doe: status1, the query will search deliverables and count all jobs that have jon doe as the employee (by using employee ID to match) and status1 as the status, it will do this for all statuses and employees. So for each employee/row, it will search deliverables six different times and return six different counts, each for the respective status.

Now however I have a new problem, I would like to sort out all employees who have a total of zero, but when I add criteria such as "not like 0" in the query builder, it asks the value of each of the count values when I run it, ex: when running it I get a pop up saying "Enter value for countstatus1" and will display a similar pop-up for all countstatus fields. It works and returns the correct value when running it without any criteria.

Thank you again!

Updates Code:

SELECT [Employees Extended].ID, [Employees Extended].[Employee Name], Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 1",1,0)) AS countstatus1, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 2",1,0)) AS countstatus2, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 3",1,0)) AS countstatus3, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 4",1,0)) AS countstatus 4, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 5",1,0)) AS countstatus5, Sum(IIf([DeliverablesPropNo]![Submitted By]=[Employees Extended]![ID] And [DeliverablesPropNo]![Status]="Status 6",1,0)) AS countstatus 6, [countstatus1]+[countstatus2]+[countstauts3]+[countstatus4]+[countstatus5]+[countstatus6] AS tot
FROM DeliverablesPropNo, [Employees Extended]
GROUP BY [Employees Extended].ID, [Employees Extended].[Employee Name];

 

[The_Doc_Man]

The discussion on JOIN is explained in this article.

Join tables and queries - Microsoft Support

Use joins in Access queries to combine records from different data sources so that each pair of records from the sources becomes one record in the query results.

support.microsoft.com

Basically, a JOIN is a way to exploit a relationship between two tables that have something in common. It is one of the ways to do a lookup from inside a query, a way that is relatively painless though it does require a bit of planning.

The danger mentioned by June7 is related to improperly constructed JOINs. A table in technical math terms is a vector, a linear set of records. If you don't like the word "vector" in this context, then try "list." A table is a list of records.

When you specifically use the syntax FROM TABLEA, TABLEB you create a matrix of all the records in TABLE A paired with all the records in TABLE B. If you have two tables of 100 records each, their "improper JOIN" produces 10,000 records. However, if they have a unique and related key field in each table, a properly constructed JOIN could give you just 100 records.

 

[June7]

You did not try CROSSTAB sql I showed?
All employees list because of LEFT JOIN.
To only show employees with deliverable data use INNER JOIN.

Consider showing all employees then use query as source for report and apply filter to report.

Regardless of which SQL is used, you need a JOIN clause, as shown in my query.

Did some edits to my prior post - might review again.