Peter Vogel answers a question about querying a customer Survey table.
I have a table that lists our customers. Some customers have agreed to participate in a survey and others haven’t. I need to calculate the number of participants who have and the number of participants who haven’t and give both numbers as a percentage of the total number of customers, grouped by city. Whether or not a customer has agreed to participate is indicated by a Yes or No in the Survey field. Is there an easy way to do this? I’m using two queries right now, and I’d like a simpler solution.
First, let’s review your initial solution. What you did was create a query to list all of the customers who had agreed to participate:
Select City, 1 As InSurvey, 0 As NoSurvey From Customers Where Survey = True
A similar query listed all of the customers who hadn’t agreed to participate:
Select City, 0 As InSurvey, 1 As NoSurvey From Customers Where Survey = False
You then joined these two queries with a Union statement to give you one list and saved it as a query called qryTotal:
Select City, 1 As InSurvey, 0 As NoSurvey From Customers Where Survey = True Union Select City, 0 As InSurvey, 1 As NoSurvey From Customers Where Survey = False
This new query produces a Recordset with three fields: City, InSurvey (containing a 1 when the customer has agreed to participate and a 0 when the customer didn’t agree), and NoSurvey (which contains a 1 when the customer didn’t agree and 0 otherwise). You then used this query as the input to your query to calculate your totals. This isn’t a bad solution. It means, among other things, that the query that calculates your totals is relatively simple:
Select City, Count(*), Sum(Survey), Sum(Survey)/Count(*) Sum(NoSurvey), Sum(NoSurvey)/Count(*) From qryTotal
I don’t know if you’ll consider my solution simpler, but it does eliminate the need for two queries. I also won’t guarantee that my version will run faster just because it uses fewer queries. Finally, my solution also won’t be as portable as yours because it makes use of some functions that are available in Access and might not be present in other database engines. I also suspect that this is one of those answers that will generate a bunch of mail to me suggesting an even better answer than the one I’m going to offer.
On that positive note, I suggest that you use an immediate if function (IIF) to distinguish between the customers who’ve agreed to participate in the survey and those who haven’t agreed. A SQL statement that generates the same result as your Union query by using the IIF function looks like this:
Select City, IIF(Survey,1,0) As InSurvey, IIF(Survey,0,1) As NoSurvey From Customers
The IIF statement takes three parameters: the test, the true result, and the false result. If the first parameter (the test) is True, then the second parameter (the true result) is returned from the function. If the test is False, the third parameter (the false result) is returned. Since the Survey already returns True or False, you don’t need an actual test—you can just use the Survey field itself as the first parameter. In my Select statement, if Survey is True, then InSurvey will be set to 1; if Survey is False, then InSurvey will be set to 0; the reverse is true for NoSurvey.
This query can now be enhanced to give you your totals. The following query, for instance, gives the totals by city for the customers who have opted in and out of the survey:
Select City, Sum(IIF(Survey,1,0)) As InSurveyTotal Sum(IIF(Survey,0,1)) As NoSurveyTotal From Customers Group By City
As I said, this does reduce the number of queries from three to one, but at the cost of a more complicated query. Before converting over to this solution, I’d also suggest that you do some time trials to see which version runs faster with your data. The beauty of SQL is that you don’t have to write much code to test out your different versions.