Aggregated query does not function in SQL

[Petros]

Hi,
I have an aggregated query (query / query) in Access and when i run it with my tables
Linked to an SQL it does not function. This is the string
SELECT XcodeCaseQuery1.XID, XcodeCaseQuery1.Category, Sum(XcodeCaseQuery1.StatusOpen) AS SumOfStatusOpen, Sum(XcodeCaseQuery1.StatusReview) AS SumOfStatusReview, Sum(XcodeCaseQuery1.StatusClosed) AS SumOfStatusClosed
FROM XcodeCaseQuery1
GROUP BY XcodeCaseQuery1.XID, XcodeCaseQuery1.Category;
Were am i doing wrong?

 

[John Big Booty]

You will also need to specify the SQL for XcodeCaseQuery1

 

[Petros]

Her is the query

SELECT Xcodes.XID, Cases.CaseID, Cases.Category, Sum(-([CaseStatus]="Open")) AS StatusOpen, Sum(-([CaseStatus]="Review")) AS StatusReview, Sum(-([CaseStatus]="Closed")) AS StatusClosed
FROM Xcodes INNER JOIN Cases ON Xcodes.XID = Cases.XID
GROUP BY Xcodes.XID, Cases.CaseID, Cases.Category;

 

[boblarson]

Her is the query

SELECT Xcodes.XID, Cases.CaseID, Cases.Category, Sum(-([CaseStatus]="Open")) AS StatusOpen, Sum(-([CaseStatus]="Review")) AS StatusReview, Sum(-([CaseStatus]="Closed")) AS StatusClosed
FROM Xcodes INNER JOIN Cases ON Xcodes.XID = Cases.XID
GROUP BY Xcodes.XID, Cases.CaseID, Cases.Category;

How can you SUM a field which is not numeric but is TEXT
Sum(-([CaseStatus]="Open"))
As well as the others. You would need COUNT not SUM.

 

[boblarson]

How can you SUM a field which is not numeric but is TEXT
Sum(-([CaseStatus]="Open"))
As well as the others. You would need COUNT not SUM.

NEVER MIND - I see what is happening with that part. It is okay. But you should probably change it to be this:
Abs(Sum([CaseStatus]="Open"))

 

[Petros]

Hi boblarsson...so i tried out eveyrthing and come to this solution

StatusOpen: Abs([CaseStatus]="Open")..it works great.

Thanks a lot!