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|Parameters and enter:
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
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!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)...

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

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.

[zmbd]

OK:
I've read and re-read your query... I've taken a look at your example db and I'm not even sure what you're after here (however, if Rabbit doesn't follow, it's got to be something exceptional - so I'm not too embarrassed); however, this is what I think you are asking for:

So work with me here.... I kind-of have to have this written down:
Do I understand the following correctly:
Take chart for "Last 3 Months..." in your example db:
In your example, the legend for this chart shows: "Month 1 = April, 2012"; "Month 2 = May, 2012"; ..."
The chart x-axis currently shows: "Month 1", "Month 2", ...
The x-axis labels are pulled from the query [3monthsgraph] which is based on the the crosstab query [3 months] based.

Is what you would like to have happen is that the x-axis labels take on the values "April, 2012"; "May, 2012"... instead of "Month 1"....

or

have I missed the mark?

-z

[Jerry Maiapu]

zmbd .
You are on the right track and you are right.

How do I achieve what you have just mentioned especially dynamic dates with stable months i.e. even if a date does not fall in a month I want the month to still show and the names "month 1", "month2" etc..converted to its corresponding month names like Jan 2010, Feb 2010 etc.. within the graph.

Assist me if you can sir.

Cheers!

Nazero

[zmbd]

I ran accross just such a thing awhile back... let me dig thru the histroy logs... problem is, I don't remember which PC I was logged in on at the time!

Remember... crosstabs are not a strong point for me:

In the meantime, while I'm hunting for that log entry, maybe something like this could be modified to work???????
http://blogs.office.com/b/microsoft-...n-reports.aspx

The only other thing I can think of is using VBA to rebuild the query on demand.

Rabbit?

[zmbd]

Here's a link to a site with an example that works for the report... I'm currently trying to understand how it works and the code behind the reports.
http://www.rogersaccesslibrary.com/f...ts.asp?TID=362
If you go back the home page for this site there's a ton of information available on the site for other questions.

[Rabbit]

That database must be in 2010 cause I can't open it.

[zmbd]

Jerry's or the one from rogers?
-z

[Rabbit]

Sorry, I should have clarified. I meant Jerry's.

[zmbd]

Tried to convert it to 2003 from 2010... some conversion errors; however, when I reopen the file in 2010 it seems ok still...

[Rabbit]

I assume this is what you're looking for. Which is, in essence, my original suggestion, but tweaked for your data.

[Jerry Maiapu]

Thanks Rabbbit.

I have to add the 6 month and 9 month trend graph on the same form but need to understand first your query content before proceeding.

Anyway thanks for your help.

..that worked.
Will post back with 3, 6 and 9 months trend graphs if i manage to achieve all for the good of those who may need.

[dsatino]

Here's how I would do it. Basically, you set the graph's rowsource as a value list rather than a query and then just build the list programatically. It takes only a small amount of code and a bit extra planning.