troubleshooting Question

Count number of Unique occurance of ONE field in and Access query

Avatar of flemingg62
flemingg62 asked on
Microsoft AccessSQL
5 Comments2 Solutions66 ViewsLast Modified:
I have a Access Query that returns a lot of data (25,360 in current set) I  want to count how may Unique Building and Floors there are (In this Case 1 building and 2 floors) and I can't figure out how to do in within 1 query (I prefer not to create lots of  a sub query to count the Building, Floor etc... separately)

Attached is sample output I'm trying to achieve and a the query design

Design.PNGRestlts.PNG
-----------
SELECT [Lookup Current Surveys].[Adept Survey UID], [Lookup Current Surveys].[Accordant SUS UID], [Lookup Current Surveys].Instance, [Lookup Curent Project].Sector, Count([SUS Source - Survey Data - Srva].ID) AS Observations, Count([SUS Source - Survey Data - Locn].Building) AS [Building Count], Count([SUS Source - Survey Data - Locn].Floor) AS [Floor Count]
FROM [SUS Source - Survey Data - Locn] INNER JOIN (([Lookup Current Surveys] INNER JOIN [Lookup Curent Project] ON [Lookup Current Surveys].[Adept Survey UID] = [Lookup Curent Project].[Adept Survey UID]) INNER JOIN [SUS Source - Survey Data - Srva] ON [Lookup Current Surveys].[Accordant SUS UID] = [SUS Source - Survey Data - Srva].Survey) ON [SUS Source - Survey Data - Locn].Code = [SUS Source - Survey Data - Srva].Location
GROUP BY [Lookup Current Surveys].[Adept Survey UID], [Lookup Current Surveys].[Accordant SUS UID], [Lookup Current Surveys].Instance, [Lookup Curent Project].Sector;
SOLUTION
als315

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 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 2 Answers and 5 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