Add the custom and calculated column to the Cross-Tab Query

[mrk777]

Hi Team!

I have an issue with Cross Tab query and following are the tables and query structure I followed:

Table:

I useed Cross-Tab query to get the results based on Time_Frame Count which resulted as follows:

Apart from 3 time frame categories you see on the top, there are 2 more ( 3 - 4 Years & > 5 Years).

Now I have few questions
1. Can we add the other 2 categories updated into the query eventhough we do not have the data for thos categories now?
2. Can we add the Total of all the catogories and bring upto the sum for each record?
3. I would like to create a form where if any cell/field is selected, automatically, this has to open the query and filter the results accordingly
For example: If I select"< 1 Year" for Accounts Payable Record - this has to open query and filter the records with time frame "< 1 year" and "Accounts Payable Function".

Attached the following image which was developed in Excel for your reference:

Please help!!

 

[ebs17]

Question1): Yes. Use fixed columns in the crosstab query.

Question 2): Yes. Either in the crosstab query itself, or you link the QT with another query.

I would like to create a form

Yes you can. It takes a little more effort, but it's solvable (maybe different than you think right now).

 

[mrk777]

So you are saying that it’s better to project the Table directly instead of cross-tab query?

I tried different ways to get the data as per the requirements, but I couldn’t do it!! Can you please suggest/help?

 

[ebs17]

Do Pat's statements have anything to do with the task presented?
Numbers are determined by counting (aggregating), better not by user input. A cross table can count very well. I think the approach is very understandable.

Fixed columns will have to be updated by a programmer.

I also see a representation in a form. Static columns are very suitable here, as is usually the case with tables/queries.