Change query column name based on text box value.

[Jerry Maiapu]

I have tried to create a monthly trend graph using cross tab query.
My aim is to extract the last 12 months starting from date specified in a text box called StartDate in a form called Filter:

The 12 months are represented by numbers 1 to 12 as column names.
The real problem is converting the column names ( 1 to 12)back to month and year.
The easiest way to convert is:
•Column 1 is [Forms]! [Filter]![StartDate]
•Column 2 is DateAdd("m",1,[Forms]! [Filter]![StartDate] )
•Column 3 is DateAdd("m",2,[Forms]! [Filter]![StartDate] ) etc. to
•Column 12 is DateAdd("m",11,[ [Forms]! [Filter]![StartDate])

However, MS access will totally refuses to accept the above column name conversion.

Does anyone know how to convert the column names based on text box values? I almost wasted most of my time cracking my head and nothing forthcoming.

I guess, declaring some variables as text, assign text box values to it and declare it as query column name could be a way forward but just at the cross roads. ??

Appreciate some direction and assistance.

[zmbd]

You might start here:

http://allenbrowne.com/ser-67.html

There's also a link about setting up a dynamic monthly cross-tab query within that page.

[Jerry Maiapu]

Thanks ZMBD for the link..
Actually I have acheived that already.

I have done the below for dynamic cross tab query based on this site:

............... ............... ............... ............... .......
For instance:

•Form: frmA
•Text Box: txtEndDate
•Table: tblSales
•Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the query parameters by menuing: Query|Parameter s and enter:
Forms!frmA!txtE ndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txt EndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Step 1: Set your queries Column Headings property to:
Column Headings: "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months.

If you need column labels in your report, use text boxes with control sources of:

Step#2=DateAdd("m",0, Forms!frmA!txtE ndDate)
=DateAdd("m",-1,Forms!frmA!tx tEndDate)
=DateAdd("m",-2,Forms!frmA!tx tEndDate)
=DateAdd("m",-3,Forms!frmA!tx tEndDate)...

............... ............... ............... ............... ........

Now the probelm is in Step#2.
Because I will be populating graphs I want to ue the result of the formating in step#2 as the column name for the query.

If it was in the reports then Step#2 would be just fine because the formating will be done on the report but I want the formating result to be the column heading for the query.

For example if date specified on form is 02/12/2011 then when formating is applied in query I want query heading to change from Mth1 to Dec 11,Mth2 to Jan 12, Mth3 to Feb 12 etc.. so that my graph can pick stats from the query.

Thanks alot..

[zmbd]

Admittedly, crosstab queries are a weak point in my database knowledge. Usually the quick and dirty Wizards work for the stuff I do. I was however hopeful that either the link to AB’s site or the one within it would help.
I’ll be following this thread as I think I have much to learn here.
-z

[Rabbit]

Pivot on the year and month instead of 1 to 12. Which I assume you're using a DateDiff to get. You only need to pivot on the 1 to 12 if you need to standardize the column names for a report or for use in another query. If you need it dynamic in the first place, then pivot it as such.

[Jerry Maiapu]

Thanks Rabbit.
Will try your suggestion and get back.

[Jerry Maiapu]

I have tried the pivoting method on year and month and does not seem to be dynamic.
I have added column heading for year as 2010,2011,2012, 2013 and but where do I pivot the months seperately in the same query as advised.
If you meant to pivot like 2011 Jan to 2012 Dec ist going to be quite messy.

I am just abit confused. please give me more hints.

[Rabbit]

You pivot them together as a single column. I don't know what you mean by messy as that's the result you want anyways.

[Jerry Maiapu]

What I meant was if I have to pivot 3 years range (2010 to 2012) then its going to be 12 months x 3 years =36 different month/ year combination I have to pivot which I do not think would be quite tidy.

[Rabbit]

I still don't understand what it is you want if not that.

[zmbd]

Maiapu,
Could you provide a stripped down example database - something with just the tables, queries, and a few rows of "made-up" example data? Usernames could be "John Doe", accounts could be "123-ABC" etc...

I suspect there's something in the underlying construction of the DB that may be making what you want very difficult to obtain.

-z

[Jerry Maiapu]

As requested, I have attached dummy data-dates & Status only. All other info is stripped off for confidentiality purposes.
My main thing to achieve is for the graph’s x-axis category to be converted to date in the” mm-yyyy” format. Thus that conversion can only happen at the query level, I guess which am struggling to figure out.
Because I was having problem trying to convert to date format in the query, I have converted separately within the form (located on the right end side of each graph) which does not look nice though.

 You’ll notice that the graphs are progressive trend graphs.
 Date End is the current date (Now ()) while start date goes back 3,6 and 9 months.
 Some fields are hidden in the main form (Filter).

Hope someone will be my eye opener from here then.

Many thanks!!

Jerry...

[Jerry Maiapu]

Hope someone will assit..Iwas still waiting for someone's comments.

[zmbd]

Jerry,
It looks like a few people have taken a look at this... you've asked for somehting that appears to be a bit tricky. I've been a tad busy myself trying to recover a badly damaged oracle database that has a ton of lab data
:) job security :)

thnx
z

[Jerry Maiapu]

Ok!..am working around it..if acheive, I will let this forum know of the solution.