crosstab query and column headings

[Stew]

Hi All,

I am working on a database that is being used to track Guaranteed Investment Certificates. I have designed a crosstab query that pulls financial company names down the left side, has months across the top and it calculates the dollar amounts in the grid. It tells me the amounts by each company. Almost Perfect.

However, there are years worth of amounts that are being calculated and I need just the amounts for the next 12 months (not for merely the current year or all years). The amounts are maturity amounts for the GICs and I need the results of the query to show me the totals for the next 12 months starting with the current month. So, most times this will result in parts of two years being displayed. I've used functions that calculate future dates but I'm running into problems with this particular structure as it isn't one column, it's twelve across the page.

I'll continue playing with Date functions but I thought I would toss this out there to see if anyone has any suggestions.

--------

I have since designed a query to base the crosstab query on to get me the date range I need. I used the following criteria for the maturity date field.

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+12,0)

This does get me the data I want but when I set up the crosstab query and report based on the retrieved information, the column headers always go from Jan to Dec. I want the current month in the first column and then the rest to follow in order. If it is July 2001, the first column should be Jul/01 and the last will be Jun/02. It needs to dynamically change based on the date that the report is run. As always, I will battle on but any suggestions would be greatly appreciated.

Thanks.

PLS

 

[Pat Hartman]

How about including another column in the query - Format(YourDate,"yyyy mmm") - and using that for the column heading. You'll need to have the year first to get the columns to sort properly.

 

[Stew]

Thanks for your reply Pat,

I tried your suggestion and it does change the order that the columns are listed but unfortunately it uses alphabetical ordering instead of by date. The columns will have the years ordered correctly but not the months. It'll go 2002 Apr - 2002 Aug and so on. I can get them in order by using a variation of the formatting:

Sort: Abs(Year([MATDATE]) & Format(Month([MATDATE]),"00"))

This will put the columns in the correct order but the Headings will be 200112 - 200201 - 200202 which is not exactly how I want it to look. I'll have to look at some code for a final report which will assign labels for the columns on the fly, maybe that's my best solution.

PLS

 

[simongallop]

Why not use Pat's answer for the headings ( or any other way that looks suitable for column headings) and then use your way to sort, but not view. All you have to do is to uncheck the box for the format that you will sort by.

 

[Stew]

Hey Harry,

I can do that without a problem for my original query which pulls the monthly data I need. The problem arises when I create a crosstab query based on the data. The crosstab will not allow me to sort the headings based on another field, only on the fields that I use to calculate my results (company name and month of maturity). So if I use a format field with yyyy and mmm the columns will mess up the monthly order because a crosstab will only use alphabetical or numerical order. If I use my formatting using only numbers, the column headings will not be user friendly (ie 200112 for dec 2001). I think I'll try using the switch function and pull the last two digits of the number format and replace the number with the appropriate month. Something like:

Expr1: Switch(Mid([sort], 5, 6) Like 12, "Dec")

It's the things you think are simple that sometimes throw you a curve.

PLS

 

[Pat Hartman]

I think you're going to have to go with numeric month values - 2001/12 to have the columns ordered properly. The other alternative is to have fixed months in the headings and show years in the rows. This method is not very pretty and can be confusing.

The reason you can order month names (Jan, Feb, Mar, etc.) properly is because you can use the column headings property to specify a list of values. The columns are always produced in the order of the column headings property. However, this method only works for fixed column headings. Yours will change every month.

 

[Stew]

Hi Pat,

I've come to the same conclusion after trying many different approaches. Thanks for your input. Til next time.

PLS

 

[simongallop]

If the data is being viewed in a report then another way is have 12 queries, each one showing data for only one month, group them all together in a final query and feed the report from them. This means that you will have Column1 company, Column2 most recent month etc. Then in your report the fields will always have the same name, the data will be in the correct order, and the important bit, the labels for the columns will be correct as you can make them a formula (ie format(Now, "mmmm-yyyy") based on todays date so that they, and the data will automaticaly adjust.

HTH