Add the custom and calculated column to the Cross-Tab Query (1 Viewer)

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

 

[Pat Hartman]

A table is safer and easier to update since the user can do it. Fixed columns will have to be updated by a programmer. Also, if you use fixed columns and the data includes extra values, that data will disappear. Using the table that defines the list to left join to the data file will not lose "extra" values although you might not like the header produced. I only use the fixed headers for things I KNOW won't change like days 1-31 and months Jan-Dec or 1-12.

 

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

 

[Pat Hartman]

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

No, I thought we were talking about fixing the column headings.

You can do that in one of two ways - the way suggested was to hard code the values in the columns property of the crosstab. I suggested keeping the values in a table and joining to that table to ensure that you have a row in the recordSource for the crosstab with every possible value.