Is it possible to use date dependent field names in a query?
I have this table and there are columns based on year and month (formate of names is: 2014,01) I need to select the columns of last month and then 4 months further down in history.
Is there a way to do that as the information in those columns I need to make calculations and graphs with.
I am hoping there is a smart and efficient way of dealing with my issue. I have been searching for the past few days to find it and I just can't.
yes I can understand the use of a Pivot table, but then I need to change the range of the information every month and that is what I would like to avoid as I need to produce like 40 different graphs and I don't want to change the data range every time manually.
I thought the crosstab query was to be used for information between tables.
My situation is:
1. I have one table with per month hour numbers
2. each month another column will be filled with new numbers
3. each month I need to produce reports
4. which I would like to be done fully automatic after the numbers are filled in.
Ah ! The design flaw.. You cannot/should not have columns like month1, month2, month3, monthN. This represents a De-Normalized structure. In a Relational Database, you store Rows against columns. With this structure, getting the information you want would be easier unlike what you have now !
Well you could do something like a "manual" crosstab, in either of two ways.... depending on what calculations you need to do...
This is the most simple way I believe.
Code:
Select Sum(IIF(Month(YourDate) = Month(Date()), Value,0)) as Thismonth
, Sum(IIF(Month(YourDate) = Month(dateadd("m", -1, Date())), Value,0)) as Prevmonth
, ... etc.
This way you fix your column names, alternating your data.
Edit: Crossposted with some new information, HOLY Design flay Paul!
Yes you fix the design flaw and NO you do not use crosstabs to fix this
What is the source of your tables? Is it within your own database? or an outside source?
Yea I might not have been too clear in my explanation I see now. Sorry for being slow
In my table I have machines and each of these machines have running hours per month. The months are in columns and the machines are the rows in the database with loads more information per machine.
I got it to work in excel, only problem there is I need to update the pivot tables as well as the calculation tables I made next to it do get what I want. Also here the problem is I need to change all pivot tables and related calculation tables each month to the latest greatest information we receive each month.
Now I was thinking what if I can change the field names in the query I use to produce the graphs with to something connected to todays date.
the columns in the table I use are: "Dealer", "Model", "2014,01", "2014,02" etc.
the rows in the table are specific machines.
I hope this explains my situation and problem better.
You can dynamicaly write SQL from code.... Something like:
Currentdb.Querydefs("YourQUeryName").sql = "Select Dealer, Model, [" & format(Date, "YYYY,MM") & "], [" etc...
However while I understand this is the way you get your data, but as you are finding, this isnt the way to store it... Instead you should make a table that holds the month number as a column and a value for each. This would make things a lot simpler....
Alternatively you can use a Union query to "correct" the data for you....
Select Dealer, Model, 2013 as myYear, 12 as myMonth, YourValue(s) from yourtable
Union all
Select Dealer, Model, 2014 as myYear, 1 as myMonth, YourValue(s) from yourtable
Union all
Select Dealer, Model, 2014 as myYear, 2 as myMonth, YourValue(s) from yourtable
Union all
etc...
Myth Buster time : Excel and Access are not the same, Excel deals with Data so much different than Access. Excel is not a Database so rules of Normalization or Data management does not exists, Access on the other hand is a proper Database and needs to comply with the nitty gritty of RDBMS.
I understand what you are saying here Paul, thing is I don't have a date in the column "2014,01" I have per machine the running hours. I need the running hours per month and calculate differences between them and create graphs. Once I got this dynamic column selector query running I can figure out the rest of the calculations which are not that difficult.
SELECT Tbl_Master.Model, Tbl_Master.[2014,02], IIf([2014,02] Between 0 And 3000,1,0) AS [0-3000], IIf([2014,02] Between 3000 And 6000,1,0) AS [3000-6000], IIf([2014,02] Between 6000 And 9000,1,0) AS [6000-9000], IIf([2014,02] Between 9000 And 12000,1,0) AS [9000-12000]
FROM Tbl_Master
GROUP BY Tbl_Master.Model, Tbl_Master.[2014,02], IIf([2014,02] Between 0 And 3000,1,0), IIf([2014,02] Between 3000 And 6000,1,0), IIf([2014,02] Between 6000 And 9000,1,0), IIf([2014,02] Between 9000 And 12000,1,0)
HAVING (((Tbl_Master.Model) Like "EH0-35") AND ((Tbl_Master.[2014,02]) Is Not Null));
Now I want the [2014,02] bit to become dynamic depending on todays year and month. I feel we are getting there.....
And YES you are correct assuming the 2014,01 means 2014 January
The problem with the SQL code I have is where do I put it in? Do I put it in a query sql? If so it is asking for DELETE, SELECT etc.
No that is THE THING, rows can be dynamicaly transformed into columns using a crosstab...
Your query that you have SHOULD be a proper crosstab as well, not the half manual version you have now.
Code:
Select Model
, [2014,02]
, Format(Int(([2014,02]/3000)), "00000") & "-" & Format(Int(([2014,02]/3000))+1, "00000") as BracketValue
From ....
Now you can do two things, I will tell you what I believe to be the easiest route in this case first....
You have existing queries for in this case Feb, containing [2014,02] as the column names.
Save these queries as qryDEFAnyname
qry being the standard prefix for any query object, if you dont use it please do and reference this: http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention
DEF as in Default, anytime you change any queries you change these default queries. Always making sure they have some default column in use, i.e. [2014,01]
Always keep this column the same across all of your DEF queries.
AnyName just any old name that makes sense to your query objective and results.
Now make a "second" query named qryAnyname, where qry again is standard and Anyname is the same as above DEFault query. Then you need this simple piece of code.
Code:
Public Sub ReplaceColumnNames()
Dim Q As QueryDef
For Each Q In CurrentDb.QueryDefs
Debug.Print Q.Name
If Left(Q.Name, 6) = "qryDEF" Then
CurrentDb.QueryDefs("qry" & Mid(Q.Name, 7)).SQL = Replace(Q.SQL, "[2014,01]", "[" & Format(Date, "YYYY,MM") & "]")
' Below line will run the query for you, you can replace this to export the query to an excel sheet
' or run append/delete queries or which ever you want to do
DoCmd.OpenQuery "qry" & Mid(Q.Name, 7)
End If
Next Q
End Sub
In this sample I made the default column be [2014,01], but you can change that to whatever default you want, as long as you keep the default the default.
Also in the DEF part offcourse you can use DEF1 or DEFM or which ever acronym you which if you need multiple different of these things.
Given your having:
Code:
(((Tbl_Master.Model) Like "EH0-35")
You are going to be running this query with multiple models i.e. EH0-35, EH0-36, EH0-37, etc.
Offcourse this works but again this is far from good practice or good design... But if it aint broke dont fix it ... on the other hand really take these things to heart and keep them in mind for future work to make things a lot more easier to work with
On top of which you really shouldnt hardcode your brackets like this, the 0-3000 etc, this may seem constant be by its definition is prown to change. This should be table driven so that if it does change it is easy to do so.
The second way goes into keeping your actual SQL statements in tables or hardcoded which I believe to be to complicated for here and now.
Well I can't get it to work as I want it. So I just forget about the excel and am working on a new database which can hold the same information, but with more lookup tables so I only need to enter data one time and this data can still be used accross different report graphs and forms.
