Solved Expression too complex in query expression 'Switch'

[cheekybuddha]

[Age] is calculated in the query from [DOB]

 

[MajP]

[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.

 

[The_Doc_Man]

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.

 

[saledo2000]

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.

 

[Dreamweaver]

Your Welcome glade to have been able to help

mick