By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,633 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,633 IT Pros & Developers. It's quick & easy.

Change query column name based on text box value.

Jerry Maiapu
100+
P: 259
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.
Jun 19 '12 #1

✓ answered by Rabbit

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

Share this Question
Share on Google+
25 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Jun 19 '12 #2

Jerry Maiapu
100+
P: 259
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..
Jun 20 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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
Jun 20 '12 #4

Rabbit
Expert Mod 10K+
P: 12,355
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.
Jun 20 '12 #5

Jerry Maiapu
100+
P: 259
Thanks Rabbit.
Will try your suggestion and get back.
Jun 20 '12 #6

Jerry Maiapu
100+
P: 259
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.
Jun 21 '12 #7

Rabbit
Expert Mod 10K+
P: 12,355
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.
Jun 21 '12 #8

Jerry Maiapu
100+
P: 259
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.
Jun 23 '12 #9

Rabbit
Expert Mod 10K+
P: 12,355
I still don't understand what it is you want if not that.
Jun 23 '12 #10

zmbd
Expert Mod 5K+
P: 5,397
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
Jun 24 '12 #11

Jerry Maiapu
100+
P: 259
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...
Attached Files
File Type: zip Graphing_pivoting.zip (175.0 KB, 158 views)
Jun 24 '12 #12

Jerry Maiapu
100+
P: 259
Hope someone will assit..Iwas still waiting for someone's comments.
Jun 28 '12 #13

zmbd
Expert Mod 5K+
P: 5,397
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
Jun 28 '12 #14

Jerry Maiapu
100+
P: 259
Ok!..am working around it..if acheive, I will let this forum know of the solution.
Jul 1 '12 #15

zmbd
Expert Mod 5K+
P: 5,397
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
Jul 3 '12 #16

Jerry Maiapu
100+
P: 259
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
Jul 5 '12 #17

zmbd
Expert Mod 5K+
P: 5,397
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?
Jul 5 '12 #18

zmbd
Expert Mod 5K+
P: 5,397
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.
Jul 5 '12 #19

Rabbit
Expert Mod 10K+
P: 12,355
That database must be in 2010 cause I can't open it.
Jul 5 '12 #20

zmbd
Expert Mod 5K+
P: 5,397
Jerry's or the one from rogers?
-z
Jul 5 '12 #21

Rabbit
Expert Mod 10K+
P: 12,355
Sorry, I should have clarified. I meant Jerry's.
Jul 5 '12 #22

zmbd
Expert Mod 5K+
P: 5,397
Tried to convert it to 2003 from 2010... some conversion errors; however, when I reopen the file in 2010 it seems ok still...
Attached Files
File Type: zip Graphing_pivoting.zip (268.5 KB, 86 views)
Jul 5 '12 #23

Rabbit
Expert Mod 10K+
P: 12,355
I assume this is what you're looking for. Which is, in essence, my original suggestion, but tweaked for your data.
Attached Files
File Type: zip Graphing_pivoting_2003format.zip (68.2 KB, 100 views)
Jul 5 '12 #24

Jerry Maiapu
100+
P: 259
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.
Jul 28 '12 #25

dsatino
100+
P: 393
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.
Attached Files
File Type: zip Graphing_pivoting.zip (244.7 KB, 116 views)
Jul 30 '12 #26

Post your reply

Sign in to post your reply or Sign up for a free account.