troubleshooting Question

Query with Distinct Count

Avatar of AndyC1000
AndyC1000 asked on
Microsoft Access
3 Comments3 Solutions166 ViewsLast Modified:
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 -

Input Table to Query
The output I'm looking for-

Desired Output
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));

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

Thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros