Need help with a query

[bigverm23]

I'm using 2 primary tables: Customer table with CustomerID and CustomerName, Contacts table with ContactID, ContactName, CustomerID, ContactStatus



ContactStatus is a lookup table with 3 statuses: 1 - Active, 2 - Inactive, 3 - Deactive

I am trying to accomplish the following:

I want to see the number of Customers that have 0 Active contacts AND (any number of Inactive/Deactive contacts), where the CustomerID matches in both. Originally I created a query that looked for a CustomerStatusID of 1 (active) AND a Count: CustomerStatusID of 0. This returned 19 results.

In reality, my query needs to show each of the 19 Customers with 0 actives, but an additional 37 Customers with either an inactive or deactive Contact, but customer may have 0 actives, but have X number of inactives/deactives, and still be counted as someone to show up on my query.

My question is how to build a query to show both the Customers with a total count of 0 Actives AND those same customers that do have any number of Inactives/Deactives. Tried Not in(1) And (>2 And <3) using the query builder in Access 2010 to no avail.

Please help!

[June7]

Sorry, delete answer, will have to revisit later.

[June7]

Ooops - meant to edit previous not create new post.

[June7]

Sorry, I've read your post a dozen times and still don't understand what you want. Provide example raw data and example output.

[bigverm23]

Sorry, I've read your post a dozen times and still don't understand what you want. Provide example raw data and example output.

CustomerID	ContactID	Name	Status
1	1	Joe	1
2	2	John	2
2	3	Kim	3
3
4	5	Zack	1
4	6	Bill	2
4	7	Bob	3

Customer1 has 1 Active Contact, Customer2 has 0 Active Contacts, X Inactives/Deactives, Customer3 has NO RECORDS, Customer4 has 1 Active, X Inactives/Deactives

Query output should show me ALL Customers that have NO Active Contacts = Customer2 and Customer3 ONLY

[aytee111]

Do this in multiple queries - keep it simple and easy to maintain.

Query1 to show the 19
Query2 which shows the 37 - bring in Query1 and do a left join, saying only if customerID is null so that you don't count the same customer twice

Get these queries working individually, then use a UNION query to join them together.

[bigverm23]

Ok tried it a different way....using a crosstab query it now shows all 3 status with a total count by company under each status header.....NOW I just need to adjust the SQL below (if possible) to say show me ONLY records where Count(lupPOCStatus.POCStatusID =1) ...can anyone help?

TRANSFORM Count(tblContacts.POCStatus) AS CountOfPOCStatus

SELECT tblCustomers.[Company Name], lupCustomerEng.FullName, lupCompSize.Size, Count(tblContacts.FullName) AS [Total Accounts]

FROM lupPOCStatus RIGHT JOIN (lupCustomerEng RIGHT JOIN ((tblCustomers LEFT JOIN tblContacts ON tblCustomers.CompanyID = tblContacts.CompanyID1) INNER JOIN lupCompSize ON tblCustomers.CompSizeID = lupCompSize.CompSizeID) ON lupCustomerEng.CEEmpID = tblCustomers.CustEngAO) ON lupPOCStatus.POCStatusID = tblContacts.POCStatus
GROUP BY tblCustomers.[Company Name], lupCustomerEng.FullName, lupCompSize.Size

ORDER BY lupCustomerEng.FullName, lupCompSize.Size

PIVOT lupPOCStatus.Status;