Link to home
Start Free TrialLog in
Avatar of AndyC1000
AndyC1000

asked on

Query with Distinct Count

Dear All,

I have a question about using the Count function.  I'm trying to count the number of records in Field 4 for each combination of field1, field 2 and field3.

The input table -

User generated image
The output I'm looking for-

User generated image
Below is my query it seems to counting the number of records in field 4 and assigning it to field2, instead of breaking it down according to field3.    

SELECT [LocationData].Field1, Count([LocationData]. Field4) AS Field4_Count, MonitoredData.Field2, MonitoredData.Field3
FROM [LocationData] INNER JOIN MonitoredData ON [LocationData].Field2 = MonitoredData.Field2
GROUP BY [LocationData].Field1, MonitoredData.Field2, MonitoredData.Field3
HAVING ((([LocationData].Field1) Is Not Null));

Open in new window


I thought the names would make it easier to explain now I don't think so!

Thanks
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial