cross tab query

Hi Iwells,

Happy thanks giving to you. Nice to see you back.

I am making a report which has an estimated close date. I had made a cross tab query, using loan amount as value and estimated close date as column heading which gives me the month order in the horizontal way.

For example.... December 2004 January 2004 Febraury 2004 >90 days.

If you look at the above example.. from current date to next three months i want the amount to be displayed monthwise but after three months i want all to be added to be put under the column heading called >90 days. Is there a way to do this using cross tab query.

Thank you

Rishy

Hi Rishy,

Can you clarify your statement just a little, You want the total of ALL amounts in the >90 days column, or just those amounts that are greater than 90 days excluding the amounts already shown in the 30,60,90 day columns? I feel pretty sure that is what you wanted. Using a crosstab query, you can get the amounts for each of the specified dates and you can get an overall total of all amounts, but if you wanted to subtract the amounts in the 30,60,90 day columns from the overall totals, you won't be able to do that with a crosstab query.

What you can do is use a select query, using the sum function for each of the 30,60,90 days and then use another expression to subtract the totals of those three columns from the overall total to get the remaining amounts to display under a column of >90. Using this method, you would get a similar view (i.e. fields for the 30,60,90 & >90) as you would with the crosstab query for your report.

If you like, you can send me your table, query and report and I will be happy to construct the query to make the totals display on your report like you want.

lwells

Hi Iwells,


just those amounts that are greater than 90 days excluding the amounts already shown in the 30,60,90 day columns. The main purpose is to fit the columns in one page and moreover not to worry about the amounts which are due after 90 days.

Thanks

Rishy