473,882 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Change query column name based on text box value.

Jerry Maiapu
259 Contributor
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
25 10725
zmbd
5,501 Recognized Expert Moderator Expert
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
259 Contributor
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..
Jun 20 '12 #3
zmbd
5,501 Recognized Expert Moderator Expert
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
12,516 Recognized Expert Moderator MVP
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
259 Contributor
Thanks Rabbit.
Will try your suggestion and get back.
Jun 20 '12 #6
Jerry Maiapu
259 Contributor
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
12,516 Recognized Expert Moderator MVP
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
259 Contributor
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
12,516 Recognized Expert Moderator MVP
I still don't understand what it is you want if not that.
Jun 23 '12 #10

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

Similar topics

2
12243
by: kj | last post by:
Here is tested schema if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO CREATE TABLE . ( (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
1
8879
by: Marble | last post by:
Hi all: In DB2 CLP mode ,how to change a column name expect to recreate a table? Thanks . Your friends.
0
1281
by: ymcj | last post by:
Hi, I'm trying to change the column name - date to Sdate in all the tables in my database. As i have many to change so i tried to search all tables and have it change automatically rather than manually however my query doesn't seem to do the job? requesting assistance from anyone is appreciated thank you! DECLARE @sSQL AS VarChar(500), -- SQL Statement @sTableName AS VarChar(100) -- TableName DECLARE CursorTable CURSOR FOR...
3
18746
by: ymcj | last post by:
Hi, I'm trying to change the column name - date to Sdate in all the tables in my database. As i have many to change so i tried to search all tables and have it change automatically rather than manually however my query doesn't seem to do the job? requesting assistance from anyone is appreciated thank you! DECLARE @sSQL AS VarChar(500), -- SQL Statement @sTableName AS VarChar(100) -- TableName DECLARE CursorTable CURSOR FOR SELECT FROM...
3
1684
by: =?Utf-8?B?Sm95?= | last post by:
I am new to VB 2005 Express Edition. I created an SQL Server database according to steps in my book, but I mispelled one of the column names (typo). Is it possible to rename the column or is it better to delete the table completely and retrace my steps to bring me back to where I am now? Thanks for your help,
6
1740
by: Inge Jones | last post by:
I have a form that dynamically generates Submit buttons. I want all the buttons to display the same text "More info" while their names are dynamically generated numeric names (they point to database record numbers). Obviously when the form returns, only one button will have been clicked. So I want to look in $_POST for a value "More info" and then ask the name of the button! The integer value of the name will be the record I display to...
3
1285
by: Yogesh Sharma | last post by:
1) Can somebody tell me How can V change the column name? 2) When you first load SQL SERVER you will startup with what all databases? I think Master database. But they have ask databases ?? Which more databases are loaded.
3
5753
by: daves1 | last post by:
Hi, I want to search for the table name and column name that have the value "ABC" . Any idea with where i could start with this one? thanks.
1
13500
by: praween4 | last post by:
friends please let me know how to change the name of the column to an already existing table
10
5024
irfanafzal
by: irfanafzal | last post by:
Hello I have saved query in MS Access "SELECT ID ,Name, Address from Data2001" I am using this query in Crystal report My question is this can I change table name in this query "DATA2001" to "DATA2002" at runtime Thanks .
0
9931
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10725
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10830
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10403
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9557
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4601
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.