Query Aggregate function error 1

[monkeysee]

I am attempting to build a query that will give the score of the last inspection for each active facility. So far I have this sql:

SELECT Food.FacilityID, Inspections.InspectionDate, Inspections.TypeID, Food.Status, 100-Sum(violation.wt) AS Score
FROM ((Inspections INNER JOIN Food ON Inspections.FacilityID=Food.FacilityID) LEFT JOIN InspectionDetails ON Inspections.InspectionID=InspectionDetails.InspectionID) LEFT JOIN Violation ON InspectionDetails.ViolationID=Violation.ViolationID
WHERE (((Inspections.TypeID)=2) AND ((Food.Status)="active"));

when I run it, I get this error:
"You tried to execute a query that does not include the specific expression 'FacilityID' as part of an aggregate function."

What am I doing wrong?

 

[PHV]

You may try this:
SELECT Food.FacilityID, Inspections.InspectionDate, Inspections.TypeID, Food.Status, 100-[!]Nz([/!]Sum(violation.wt[!]),0)[/!] AS Score
...
WHERE (((Inspections.TypeID)=2) AND ((Food.Status)="active"))
[!]GROUP BY Food.FacilityID, Inspections.InspectionDate, Inspections.TypeID, Food.Status[/!];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[monkeysee]

Excellent! Works great! Thanks a bunch!
Now....I need to have this query return the last inspection(Inspections.InspectionDate)with it's score after a date parameter. How would I work that in?

thanks!

 

[PHV]

You may try this:
...
WHERE Inspections.TypeID=2 AND Food.Status='active'
AND Inspections.InspectionDate=(SELECT Max(InspectionDate) FROM Inspections WHERE TypeID=2 AND FacilityID=Food.FacilityID)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[monkeysee]

That is Great! We are getting there.
What we have now IS returning the LAST inspection date of all facilities within the criteria.

Now..How can I add the date parameter such as: Between [From mm/dd/yyyy] And [Thru mm/dd/yyyy]

As I need this query to calculate for prior calendar years.

Thanks so much for your help - IT'S GREATLY APPRECIATED!

 

[lespaul]

WHERE Inspections.TypeID=2 AND Food.Status='active'
AND Inspections.InspectionDate=(SELECT Max(InspectionDate) FROM Inspections WHERE TypeID=2 AND FacilityID=Food.FacilityID) AND InspectionDate Between [Enter Start Date] And [Enter End Date]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[monkeysee]

PHV & Leslie,
Thanks, this is where I am having the problem. When I put the date parameter in, it kicks out several facilities that falls within the criteria. I have double checked the data and there is no difference between the omitted facilities and the included facilities. Do you have any idea why this may be happening?

 

[lespaul]

Can you provide a sample of the included records and the omitted records? Maybe we can help discover why you're not getting the results you expect.

Leslie

 

[monkeysee]

Here goes: This is just a portion of the query before the "Last" function is used.

FacilityID InspectionDate TypeID Status
14 1/24/2006 2 Active
14 8/1/2006 2 Active
27 8/28/2006 2 Active
27 1/26/2006 2 Active
30 9/11/2006 2 Active
30 12/14/2006 2 Active
44 9/5/2006 2 Active
44 1/23/2006 2 Active



After the "last" function is put into the query, Facility #14 and #44 are not included.
FacilityID is a unique number.
InspectionDate uses an input mask
TypeID is a number not text
Status is text

I have built the following query's. The First returns all facilities with the specific criteria. The Second query when using the 'last' function gives me the data omitting several facilities which appear in the first.

First:
SELECT Food.FacilityID, Food.BusinessName, Inspections.InspectionDate, InspectionDetails.InspectionID, 100-Sum(violation.wt) AS Score, Food.Status, Inspections.TypeID
FROM ((Violation INNER JOIN InspectionDetails ON (Violation.ViolationID = InspectionDetails.ViolationID) AND (Violation.ViolationID = InspectionDetails.ViolationID)) INNER JOIN Inspections ON InspectionDetails.InspectionID = Inspections.InspectionID) INNER JOIN Food ON Inspections.FacilityID = Food.FacilityID
GROUP BY Food.FacilityID, Food.BusinessName, Inspections.InspectionDate, InspectionDetails.InspectionID, Food.Status, Inspections.TypeID
HAVING (((Inspections.InspectionDate) Between [From mm/dd/yyyy] And [Thru mm/dd/yyyy]) AND ((Food.Status)="active") AND ((Inspections.TypeID)=2) AND ((100-Sum([violation].[wt])) Is Not Null));

Results of the first query is the same as above.


Second:

SELECT Food.FacilityID, Food.BusinessName, Last(Inspections.InspectionDate) AS LastOfInspectionDate, Last(InspectionDetails.InspectionID) AS LastOfInspectionID, 100-Sum(violation.wt) AS Score, Food.Status, Inspections.TypeID
FROM ((Violation INNER JOIN InspectionDetails ON (Violation.ViolationID = InspectionDetails.ViolationID) AND (Violation.ViolationID = InspectionDetails.ViolationID)) INNER JOIN Inspections ON InspectionDetails.InspectionID = Inspections.InspectionID) INNER JOIN Food ON Inspections.FacilityID = Food.FacilityID
GROUP BY Food.FacilityID, Food.BusinessName, Food.Status, Inspections.TypeID
HAVING (((Last(Inspections.InspectionDate)) Between [From mm/dd/yyyy] And [Thru mm/dd/yyyy]) AND ((Food.Status)="active") AND ((Inspections.TypeID)=2) AND ((100-Sum([violation].[wt])) Is Not Null));

Results of this query only gives me info for facility #30 out of the same example above.

WIERD!

 

[PHV]

The date criteria must be applied to the subquery:
WHERE Inspections.TypeID=2 AND Food.Status='active'
AND Inspections.InspectionDate=(SELECT Max(InspectionDate) FROM Inspections WHERE TypeID=2 AND FacilityID=Food.FacilityID AND InspectionDate Between [Enter Start Date] And [Enter End Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886