query worked a second ago, now doesn't, i'm tired... 1

[Ime532]

SELECT customers.[hospital name], customers.City, customers.State, customers.[Account Type], customers.[Software Version], customers.LBS, customers.[Ship Receive Bill], customers.Labor, customers.Maintenance, customers.Order, customers.PackRoom, customers.ItemTracking, IIF(hardware.type="PC", count(hardware.type), 0)
FROM customers, hardware
WHERE [Hospital Name] Like "*Crothal*" And customers.[hospital number]=hardware.[hospital number] And hardware.[hardware status] Like "*Current*"
Group By customers.[hospital name], customers.City, customers.State, customers.[Account Type], customers.[Software Version], customers.LBS, customers.[Ship Receive Bill], customers.Labor, customers.Maintenance, customers.Order, customers.PackRoom, customers.ItemTracking;

The IIF statement is causing the problem. Access says it should be part of an aggregate function and therefore should be included in the group by clause. Obsiously that's not right. I JUST had this query working a second ago and I don't know what i'm doing wrong this second time. Thanks.

 

[Ime532]

If I add hardware.type to the query it works, but defeats the purpose of the query:

SELECT customers.[hospital name], customers.City, customers.State, customers.[Account Type], customers.[Software Version], customers.LBS, customers.[Ship Receive Bill], customers.Labor, customers.Maintenance, customers.Order, customers.PackRoom, customers.ItemTracking, IIF(hardware.type="PC", count(hardware.type), 0)
FROM customers, hardware
WHERE [Hospital Name] Like "*Crothal*" And customers.[hospital number]=hardware.[hospital number] And hardware.[hardware status] Like "*Current*"
Group By customers.[hospital name], customers.City, customers.State, customers.[Account Type], customers.[Software Version], customers.LBS, customers.[Ship Receive Bill], customers.Labor, customers.Maintenance, customers.Order, customers.PackRoom, customers.ItemTracking;

I want the two different hardware.type's to appear on the same record, not two seperate.

 

[lupins46]

You need to include two columns in your query to show two counts.
IIF(hardware.type="PC", 1,0)

and

IIF(hardware.type<>"PC", 1,0)

and then usethe Count option on each of these columns.