Crazy field alias- possible?

[Maarkr]

Don't ask why...format needs to stay this way for a graph.
I have fields in the table from a s/s: BBL2007 BBL2008 KWH2007 KWH2008...
I want to show this current years' BBL (currently BBL2008, but next year
will change to BBL2009) and last years' BBL (BBL2007...), but if you set an
alias, like ThisYrBBL: "[" & "BBL" & Year(Date()) & "]" -or- LastYrBBL:
"[" & "BBL" & Year(Date()-365) & "]" you get the field name in the datasheet
view [BBL2008]. Is it possible to concatenate portions of a field alias so
it is recognized as a field name?

 

[Beetle]

You could do it in code with something like the following. It could be
run from, for example, the Click event of a command button. This
example assumes a table name of tblYourTable and a stored query
named qryYourQuery. Make a backup first if you decide to try this.

Dim strSQL As String
Dim strFieldName1 As String, strFieldName2 As String

strFieldName1 = "[BBL" & CStr(Year(Date)) & "]"

strFieldName2 = "[BBL" & Format(DateAdd("yyyy", -1, Date), "yyyy") & "]"

strSQl = "SELECT " & strFieldName1 & " AS ThisYrBBL, " & strFieldName2 _
& " AS LastYrBBL FROM tblYourTable;"

CurrentDb.QueryDefs("qryYourQuery").SQL = strSQL

DoCmd.OpenQuery "qryYourQuery"

 

[Clifford Bass]

Hi,

Sean's suggestion is a good one. But I would contend that the data
desparately needs to be normalized and that you can work with it in that
form. Do you really want to get into modifying the table and the associated
forms every year? What happens when you are gone? What about when someone
wants to run an older report/graph such as near the beginning of 2009,
wanting 2007 and 2008?

I would suggest something like this:

tblBBL_KWH
DATA_YEAR
BBL
KWH

To get the ThisYear and LastYear information use this:

PARAMETERS [Enter Start Year:] Short;
SELECT LastYear.DATA_YEAR AS LastYear, LastYear.BBL AS LastYearBBL,
LastYear.KWH AS LastYearKWH, ThisYear.DATA_YEAR AS ThisYear, ThisYear.BBL AS
ThisYearBBL, ThisYear.KWH AS ThisYearKWH
FROM tblBBL_KWH AS LastYear, tblBBL_KWH AS ThisYear
WHERE (((LastYear.DATA_YEAR)=IIf(IsNull([Enter Start
Year:]),Year(Date())-1,[Enter Start Year:])) AND
((ThisYear.DATA_YEAR)=[LastYear].[DATA_YEAR]+1));

It will prompt for a start year. Leave blank to start with the prior
year.

If you need all of the years worth of data denormalized, you could do
this (there may well be better ways):

Create a query named qryBBL_KWH:

SELECT tblBBL_KWH.DATA_YEAR AS THIS_DATA_YEAR, [DATA_YEAR]+1 AS
NEXT_DATA_YEAR, tblBBL_KWH.BBL, tblBBL_KWH.KWH
FROM tblBBL_KWH;

Then create another query:

SELECT [2006].BBL, [2006].KWH, [2007].BBL, [2007].KWH, [2008].BBL,
[2008].KWH, [2009].BBL, [2009].KWH, [2010].BBL, [2010].KWH
FROM (((qryBBL_KWH AS 2006 LEFT JOIN qryBBL_KWH AS 2007 ON
[2006].NEXT_DATA_YEAR=[2007].THIS_DATA_YEAR) LEFT JOIN qryBBL_KWH AS 2008 ON
[2007].NEXT_DATA_YEAR=[2008].THIS_DATA_YEAR) LEFT JOIN qryBBL_KWH AS 2009 ON
[2008].NEXT_DATA_YEAR=[2009].THIS_DATA_YEAR) LEFT JOIN qryBBL_KWH AS 2010 ON
[2009].NEXT_DATA_YEAR=[2010].THIS_DATA_YEAR
WHERE ((([2006].THIS_DATA_YEAR)=2006));

This one assumes your data starts in 2006 and ends in 2010. Years at
the end can be absent. But years at the beginning must exist and be
sequential. That is, you can have data for 2006, 2007 and 2008. But you
cannot have data for 2006 and 2008 with 2007 missing. You can modify it to
your actual information. And you can set the field aliases if you desire.
You could extend it out to some well distant year or you could use Sean's
method to update it once a year. Maybe put that process on a button or menu
option that the users can click as a setup step for the new year.

Clifford Bass

Don't ask why...format needs to stay this way for a graph.
I have fields in the table from a s/s: BBL2007 BBL2008 KWH2007 KWH2008...
I want to show this current years' BBL (currently BBL2008, but next year
will change to BBL2009) and last years' BBL (BBL2007...), but if you set an
alias, like ThisYrBBL: "[" & "BBL" & Year(Date()) & "]" -or- LastYrBBL:
"[" & "BBL" & Year(Date()-365) & "]" you get the field name in the datasheet
view [BBL2008]. Is it possible to concatenate portions of a field alias so
it is recognized as a field name?

 

[Maarkr]

thanks for the help, but that's why i said 'Don't ask why...' it is data
that must be formatted in the same way to work in a chart...it's NOT
normalized...pulled in from Excel. I just needed to figure a way to format
the query so it will work year-to-year instead of changing the query for the
appropriate year annually.

Hi,

Sean's suggestion is a good one. But I would contend that the data
desparately needs to be normalized and that you can work with it in that
form. Do you really want to get into modifying the table and the associated
forms every year? What happens when you are gone? What about when someone
wants to run an older report/graph such as near the beginning of 2009,
wanting 2007 and 2008?

I would suggest something like this:

tblBBL_KWH
DATA_YEAR
BBL
KWH

To get the ThisYear and LastYear information use this:

PARAMETERS [Enter Start Year:] Short;
SELECT LastYear.DATA_YEAR AS LastYear, LastYear.BBL AS LastYearBBL,
LastYear.KWH AS LastYearKWH, ThisYear.DATA_YEAR AS ThisYear, ThisYear.BBL AS
ThisYearBBL, ThisYear.KWH AS ThisYearKWH
FROM tblBBL_KWH AS LastYear, tblBBL_KWH AS ThisYear
WHERE (((LastYear.DATA_YEAR)=IIf(IsNull([Enter Start
Year:]),Year(Date())-1,[Enter Start Year:])) AND
((ThisYear.DATA_YEAR)=[LastYear].[DATA_YEAR]+1));

It will prompt for a start year. Leave blank to start with the prior
year.

If you need all of the years worth of data denormalized, you could do
this (there may well be better ways):

Create a query named qryBBL_KWH:

SELECT tblBBL_KWH.DATA_YEAR AS THIS_DATA_YEAR, [DATA_YEAR]+1 AS
NEXT_DATA_YEAR, tblBBL_KWH.BBL, tblBBL_KWH.KWH
FROM tblBBL_KWH;

Then create another query:

SELECT [2006].BBL, [2006].KWH, [2007].BBL, [2007].KWH, [2008].BBL,
[2008].KWH, [2009].BBL, [2009].KWH, [2010].BBL, [2010].KWH
FROM (((qryBBL_KWH AS 2006 LEFT JOIN qryBBL_KWH AS 2007 ON
[2006].NEXT_DATA_YEAR=[2007].THIS_DATA_YEAR) LEFT JOIN qryBBL_KWH AS 2008 ON
[2007].NEXT_DATA_YEAR=[2008].THIS_DATA_YEAR) LEFT JOIN qryBBL_KWH AS 2009 ON
[2008].NEXT_DATA_YEAR=[2009].THIS_DATA_YEAR) LEFT JOIN qryBBL_KWH AS 2010 ON
[2009].NEXT_DATA_YEAR=[2010].THIS_DATA_YEAR
WHERE ((([2006].THIS_DATA_YEAR)=2006));

This one assumes your data starts in 2006 and ends in 2010. Years at
the end can be absent. But years at the beginning must exist and be
sequential. That is, you can have data for 2006, 2007 and 2008. But you
cannot have data for 2006 and 2008 with 2007 missing. You can modify it to
your actual information. And you can set the field aliases if you desire.
You could extend it out to some well distant year or you could use Sean's
method to update it once a year. Maybe put that process on a button or menu
option that the users can click as a setup step for the new year.

Clifford Bass

Don't ask why...format needs to stay this way for a graph.
I have fields in the table from a s/s: BBL2007 BBL2008 KWH2007 KWH2008...
I want to show this current years' BBL (currently BBL2008, but next year
will change to BBL2009) and last years' BBL (BBL2007...), but if you set an
alias, like ThisYrBBL: "[" & "BBL" & Year(Date()) & "]" -or- LastYrBBL:
"[" & "BBL" & Year(Date()-365) & "]" you get the field name in the datasheet
view [BBL2008]. Is it possible to concatenate portions of a field alias so
it is recognized as a field name?

 

[Clifford Bass]

Hi Maarkr,

You are welcome. You will note that I did not ask why. To my
knowledge column aliases cannot be dynamically created within a query. So
you are stuck with something like Sean's solution.

A little bit later.... There was something about that that got the
brain thinking in a different direction. Instead of dynamic aliases, how
about a dynamic selection of the field to use? Try:

PARAMETERS [Report Year:] Short;
SELECT IIf(IsNull([Report Year:]),Year(Date()),[Report Year:]) AS
ReportYear, tblEnergy.RecordID, DLookUp("BBL" &
[ReportYear],"tblEnergy","RecordID = " & [RecordID]) AS ThisYearBBL,
DLookUp("BBL" & [ReportYear]-1,"tblEnergy","RecordID = " & [RecordID]) AS
LastYearBBL, DLookUp("KWH" & [ReportYear],"tblEnergy","RecordID = " &
[RecordID]) AS ThisYearKWH, DLookUp("KWH" &
[ReportYear]-1,"tblEnergy","RecordID = " & [RecordID]) AS LastYearKWH
FROM tblEnergy;

Substitute your table/column names as needed. This will prompte for a
report year. If one is entered, it will use that one so you can run
historical reports. If nothing is entered it will use the current year. It
does require a unique way of identifying an individual row; RecordID in the
above example.

Clifford Bass