473,836 Members | 1,471 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
25 10719
zmbd
5,501 Recognized Expert Moderator Expert
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
259 Contributor
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, 190 views)
Jun 24 '12 #12
Jerry Maiapu
259 Contributor
Hope someone will assit..Iwas still waiting for someone's comments.
Jun 28 '12 #13
zmbd
5,501 Recognized Expert Moderator Expert
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
259 Contributor
Ok!..am working around it..if acheive, I will let this forum know of the solution.
Jul 1 '12 #15
zmbd
5,501 Recognized Expert Moderator Expert
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
259 Contributor
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
5,501 Recognized Expert Moderator Expert
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
5,501 Recognized Expert Moderator Expert
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
12,516 Recognized Expert Moderator MVP
That database must be in 2010 cause I can't open it.
Jul 5 '12 #20

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

Similar topics

2
12239
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
1280
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
18745
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
1680
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
1739
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
5752
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
13498
by: praween4 | last post by:
friends please let me know how to change the name of the column to an already existing table
10
5023
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
9816
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
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10588
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
9371
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...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5647
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...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4448
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

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.