Derive Report Column Label from Query result

  • Thread starter Thread starter Andre Laplume via AccessMonster.com
  • Start date Start date
A

Andre Laplume via AccessMonster.com

We have reports that have columns for months (JulYY - JunYY) going across the
top of the page. Depending on how the report is run, the year of the month
may differ....ie a fiscal 03 run, an 04 run etc. This has nececitated
updating the column labels (ex: Jul 03 to Jul 04, Aug 03 to Aug 04 etc etc).
I came up with 12 queries (one for each month) that will always result in ONE
returned record with one field: label containing what I would want to see as
the lable for that particular month on the report. Is there a way to
link/derive the report column label to the query result? In other words I'd
have 12 text boxes/labels across the top of the report page and each would
point to it's own query which would return the label text I'd want to see on
the report page.


On the box's properties it looks like the Control Source will let you drill
down to a query and even select the defined 'label' value however when I open
the report it behaves as if the query is non-existent.
 
Is this report the result of a crosstab query or do you actually have a
table with months as field names?
 
No such luck. The data table is vertical and essentially has a column for
each month like: Jul$, Aug$ etc etc. The year the report is run for is
determined in selection criteria.

Between you and me I just wanted to label the months across the Access report
as: Jun - Jul. I'd take care of identifying the Fiscal Year by prompting for
a report title in the query (ie Fiscal 2003), storing it in a column and
reporting the column on the report. That would be too simple though! No,
they MUST have the month/year above each month column of data. (Jul YY, Aug
YY etc.)

Essentially I added 12 text boxes above each data column. All I really want
to do is have the value for each of the 12 boxes derived from one of 12
queries that return one row with one field: label. There is a query for each
month since a fiscal year will cross 2 calendar years.

I know this sounds a bit backward but it is an old report and we do not want
to invest in reworking it. I am just sick of having to 'relabel' the column
headers every time it is run for a different year....AND it looks like the
TextBox ControlSource lets you drill down to a query. In fact when I do so
it puts this in the box: = [Query12]![label] . Yet when I run it it
prompts for value for Query12 instead of running the query and returning the
value.
 
I would normalize the data with a union query and then create a crosstab
query and report. I have posted solutions that dynamically create the column
labels in the report. Search google groups on:
hookom relative months crosstab access
 
thanks, normalizing is out unles we redo this report and the other 5 like it
and I doubt I will get the time. Plus there are other issues I will not go
into.

I'll search..thanks!
 
Back
Top