2

I work in a call center and am trying to generate a crosstab query in MS Access 2016 that will show sales conversion rates by month and by advertising source. In this query I want the columns to represent months, the rows to represent advertising sources and the values to be the sales conversion rate for that advertising source and month.

I tried setting the Total field to Expression and get this error: Your query does not include the specified expression Close Rate as part of an aggregate function. Close Rate is the field name I've used for the sales conversion rate item I described above.

If I set the Total field to Group By I get the below error.

Crosstab 'Group By' Error

I don't want to sum, count, etc... my value field here though, I simply want to display a value I've specified for the intersection of my specified row and column field. Is this possible?

Here is the SQL code for the crosstab query while I have the Total field for the value set to Expression.

TRANSFORM [Qry_Closing by Month by Campaign].[Close Rate]
SELECT [Qry_Closing by Month by Campaign].PromoCode
FROM [Qry_Closing by Month by Campaign]
GROUP BY [Qry_Closing by Month by Campaign].PromoCode
PIVOT [Qry_Closing by Month by Campaign].Month;

Here is the SQL for the query below the cross tab query.

SELECT [dd campaign_Five9_Final].Month, [dd campaign_Five9_Final].PromoCode, Format(Round([Total Sales]/[sumofcalls],4),"Percent") AS [Close Rate]
FROM [dd campaign_Five9_Final];

There are additional queries below this that I can post if necessary.

Thanks ahead of time for any help!

Andre
  • 26,751
  • 7
  • 36
  • 80
Andrew Wetzel
  • 33
  • 6

1 Answers1

3

A crosstab query always has a GROUP BY clause, and therefore the elements to be displayed as values always need an Aggregate function. There is no way around it.

Think about it this way:

If your base query always returns only one single value per Month & PromoCode, then it doesn't matter which aggregate function you use (well, except COUNT). With only one value, MIN, MAX, SUM, AVG will all simply return that one value.

If there can be multiple values, it's obvious that they need to be aggregated, since there is only one output "cell" for each Month & PromoCode.

Since it's a percentage value, AVG might the best choice then.

Andre
  • 26,751
  • 7
  • 36
  • 80