CODEWITHSUNDEEP
sqlms-accesscount
user1688175
user1688175

Reputation:

Count issue with MS Access

The following query works great in MS Access, however I actually wanted to get only the Main Project field plus the count for Name. Something like:

SELECT Sheet1.[Main project], Count(Sheet1.[Name]) AS Total

When I do that I get the following error:

"You tried to execute a query that does not include the specified expression 'Main project' as part of the aggregated function'

Am I doing something wrong in the join? Any idea what is not correct?

SELECT Sheet1.[Main project], Sheet1.[Main link], Sheet1.[Name]
FROM Sheet1
INNER JOIN Sheet1 AS Sheet1_1 

ON (Sheet1.[Main link] = Sheet1_1.[Main link]
AND Sheet1.[Main project] = Sheet1_1.[Main project])

WHERE Sheet1_1.[Name] ='0.4 Rollout plan approved'
AND Sheet1_1.[%compl#] = 100
AND (Sheet1_1.[Task class] <> '***Sammelvorgang' AND Sheet1_1.[Task class] <> '' AND  Sheet1_1.[Task class] <> 'M D-Muster')

AND Sheet1.[Main project] = 'AUDI AG, Ingolstadt'
AND (Sheet1.[Task class] <> '***Sammelvorgang' AND Sheet1.[Task class] <> '' AND  Sheet1.[Task class] <> 'M D-Muster')
AND (Sheet1.[Name] NOT LIKE '0.*' AND Sheet1.[Name] NOT LIKE '1.*')
AND Sheet1.[Start] < DateAdd("d",-7,Now())

Upvotes: 1

Views: 96

Answers (1)

dan1111
dan1111

Reputation: 6566

SELECT Sheet1.[Main project], Count(Sheet1.[Name]) AS Total
    FROM Sheet1
    GROUP BY Sheet1.[Main project]

Aggregate functions like Count() only work automatically when you want a total count of everything in the table. If you want to break it down into more specific groupings, you need a GROUP BY statement to tell the database how to break it down.

Integrating that into your larger query should not be a problem: just note that the GROUP BY clause comes after the WHERE clause; thus you should add it at the end.

Upvotes: 1

Related Questions