Solved Expression too complex in query expression 'Switch' (1 Viewer)


You've got your good things, and you've got mine.
Local time
Yesterday, 20:36
May 21, 2018
[Age] is calculated in the query from [DOB]
Thanks, I updated what I meant. What I said originally was totally unclear for what I meant to say.


Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 19:36
Feb 28, 2001
We are dancing around on the head of a pin here.

First and foremost, this is a cross-tab query, which is still easy enough to do. The only problem is that the cross-tab based on age in years would normally show those ages numerically.

SO... create a table that has records in it <1, "One">, <2, "Two">, <3, "Three"> etc for as many number-to-name cases as you wish. You could make a tuple that said <0,"Zero"> if that is important. Make the number the PK and let the name be a dependent variable (no key required).

Build a query that computes the age in years for each person and shows almost everything else you want total or count. But JOIN that query to the table of number-to-name translation. Call that JOIN query Layer1. Now build a Layer2 query that builds the crosstab of Layer1 including the text-name column as one of the attributes you are "crossing." You might have to diddle a bit with the order of items in the SELECT clause to get the names in the right place. You might also have to diddle with that a bit with a WHERE clause that only shows records with non-zero count or only shows records with the numeric part less than 21 or whatever.


Registered User.
Local time
Today, 01:36
Jan 21, 2013
You could use a cross tab query for a sub report

You said you were displaying totals for an age are there more than 1 row like girls, boys?
Hi MickJav
Thank you for your idea. I did use cross tab of my first query and manage to get result.
Than you all guys for helping me to solve some problems I had. More is coming to tight up my project.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom