Crosstab And Left Join On Value Range

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hey there. I have a query qryAccounts that counts how many
client accounts are over, say, $5,000 for each of my
company's sales territories.

Territory Accounts
Boston 4
Charlotte 11
Atlanta 12
Denver 27
etc.

I created a table tblAccountSizes that looks like this:

Low High
0 5
6 10
11 15
16 20
21 100

I'm trying to create a crosstab query with a join between
qryAccounts and tblAccountSizes that will show me
Territory as my row, the ranges (Low-High) as my columns,
and the number of Territories that have accounts between
the ranges above as my values. Any suggestions would be
fantastic. Thanks!

Territory 0-5 6-10 11-15 16-20 21-100
Boston 4
Charlotte 11
Atlanta 12
Denver 27
 
I would add a "title" field to tblAccountSizes that could be used as the
Column Heading. When you create your crosstab, don't use any join lines. Set
the criteria under the Accounts column to Between [Low] and [High].
 
Back
Top