Access Select Count Union Query - Need Help with SQL

Question

Hi,

I have an Access union query that is supposed to select all results in the table that meet two criteria (falling between a particular date range and having a certain value in the CustomerName field) and calculate what percentage of it is profitable or not (so, a value more or less than zero in my Profitability$ field). The first part of the union query calculates what percentage isn't profitable, the second part of the union query calculates what percentage is, and I use these two values to make a pie chart. Currently my SQL looks like this:

PARAMETERS [Enter Start Date (mm/dd/yyyy):] DateTime, [Enter End date (mm/dd/yyyy):] DateTime;

SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE CustomerName = 'MERCURY PLASTICS OF' OR 'MERCURY PLASTICS, IN'

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName = 'MERCURY PLASTICS OF' OR 'MERCURY PLASTICS, IN'

AND [Profitability$]< 0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):]

UNION SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE CustomerName = 'MERCURY PLASTICS OF' OR 'MERCURY PLASTICS, IN'

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName = 'MERCURY PLASTICS OF' OR 'MERCURY PLASTICS, IN'

AND [Profitability$]>=0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):];

It's working -- that is to say it's calculating -- but it's coming up with incorrect numbers. Previously, before the CustomerName category was relevant and only the date range mattered, I was using this:

PARAMETERS [Enter Start Date (mm/dd/yyyy):] DateTime, [Enter End date (mm/dd/yyyy):] DateTime;

SELECT COUNT(*)/

    (SELECT COUNT(*)       

     FROM Untitled

     WHERE RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE [Profitability$]< 0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):]

UNION SELECT COUNT(*)/

    (SELECT COUNT(*)       

     FROM Untitled

     WHERE RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE [Profitability$]>=0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):];

Which worked perfectly. Does anyone have some advice to offer?

Answer 1

You can use NOT IN to exclude items:

PARAMETERS [Enter Start Date (mm/dd/yyyy):] DateTime, [Enter End date (mm/dd/yyyy):] DateTime;

SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE CustomerName NOT IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName NOT IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

AND [Profitability$]< 0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):]

UNION SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE CustomerName NOT IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName NOT IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

AND [Profitability$]>=0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):];

Answer 2

Scott's suggestion to use IN() is right on the mark; do so!

But to explain why your current query isn't working, you need to see how OR and AND work in criteria expressions. It's sort of like how + and * work in arithmetic: just as 2 + 3 * 5 is 17 (calculate the 3*5 first, then add 2 to the result), so AND is applied first, and that result is used in the OR expression. To force the order of operation you must use parentheses on the <<<< lines:

PARAMETERS [Enter Start Date (mm/dd/yyyy):] DateTime, [Enter End date (mm/dd/yyyy):] DateTime;

SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE (CustomerName = ('MERCURY PLASTICS OF' OR 'MERCURY PLASTICS, IN')   <<<<<

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName = ('MERCURY PLASTICS OF' OR 'MERCURY PLASTICS, IN')    <<<<<

AND [Profitability$]< 0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):]

and similarly for the UNION. But the IN() clause is clearer and probably more efficient so go with it.

Depending on the data you might also be able to use

WHERE CustomerName LIKE "MERCURY PLASTICS*"

to pick up both names.

Answer 3

It works! Thank you so much, Scott. Is there such a thing as an "OUT" clause, where I could exclude those Mercury Plastics values? I'd like to be able to do one query for these values and another for the rest of the table, excluding them.

Answer 4

Try using an IN clause:

PARAMETERS [Enter Start Date (mm/dd/yyyy):] DateTime, [Enter End date (mm/dd/yyyy):] DateTime;

SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE CustomerName IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

AND [Profitability$]< 0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):]

UNION SELECT COUNT(CustomerName)/

    (SELECT COUNT(CustomerName)       

     FROM Untitled

     WHERE CustomerName IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

     AND RptDateStart = [Enter Start Date (mm/dd/yyyy):]

     AND RptDateEnd = [Enter End date (mm/dd/yyyy):])*100 AS Percentage

FROM Untitled

WHERE CustomerName IN ('MERCURY PLASTICS OF', 'MERCURY PLASTICS, IN')

AND [Profitability$]>=0

AND RptDateStart = [Enter Start Date (mm/dd/yyyy):] 

AND RptDateEnd = [Enter End date (mm/dd/yyyy):];