Reports / Year-End Roll Over

[Den]

Group:

I'm new to Access - and am sure that there is a way to achieve what I want
.... I just don't know how ... your help would be appreciated.

I'll simplify the problem that I have for ease of clarity!

Using specifics .....
During this year (say '04) I need my report to print data from table
fields F04,F05,F06,F07,F08,F09.
During next year (say '05) I need my report to print data from table
fields F05,F06,F07,F08,F09,F10.
During the year after (say '06) I need my report to print data from table
fields F06,F07,F08,F09,F10,F11.

In the general case ...
During the year n (n<='04) I need my report to print data from table
fields Fn,Fn+1,Fn+2,Fn+3,Fn+4,Fn+5.

My solution has been (for '04) to set the report recordsource as:
SELECT F04 AS C1, F05 AS C2, F06 AS C3, F07 AS C4, F08 AS C5, F09 AS C6
FROM TABLE;
and to then create my report using the SQL aliaii C1,C2,C3,C5,C5,C6. This
works.

At the end of the year, all I need to do is to change the SQL statement in
the report recordsource. However, I really need to automate this.

What I'd like to do is to be able to put the report recordsource SQL in as a
string ... like this:
="SELECT F"&right(year(date()),2)&" AS C1, F"&right(year(date())+1,2)&" AS
C2, F"&right(year(date())+2,2)&" AS C3, F"&right(year(date())+3,2)&" AS C4,
F"&right(year(date())+4,2)&" AS C5, F"&right(year(date())+5,2)&" AS C6 FROM
TABLE;" ... okay inelegant but it should do the job ... but Access doesn't
want to know.

My guess is that I am on the right tracks, but I'm missing a step or
something. How do I dynamically set the report recordsource from a string?

Could any wise person out there point me in the correct direction.

Cheers

D

 

[Duane Hookom]

Your main issue is that you are using field names to store data values. Is
there any way that you could modify your table structure to be more
normalized?

 

[Den]

Sadly not ... the underlying table structures are not open to modification
.... legacy issues!

D

 

[Duane Hookom]

I would at least temporarily push the records into normalized tables for
reporting or create a UNION query that normalizes the data.

 

[Den]

Hi Duane

I agree that the formal and very proper way to do this is to have a fully
normalised database, however (sadly) I'm not choosing where I start from. I
also agree that I could create a scratch table with normalised data as the
source of the report. However, this is going to require a fair amount of
scratch space - for me this is achievable but not desirable.

I don't mean to sound ungrateful for the sound advice that you have given,
but, I return to my original question which was (essentially) is it possible
to set the report recordsource to a SQL query embedded in a string
expression: ="SELECT ....;", and if so, how?

Cheers

D

 

[Duane Hookom]

You could create a loop in code that selects the proper fields and gives
them an appropriate alias. Once you have built the sql statement, you can
apply it to a saved query with code like:
Currentdb.QueryDefs("qselYourQuery").SQL = strSQL

 

[Den]

Duane

That's great ... thank you ... that gives me a solution that I can use.

To make things even simpler, is it somehow possible to set the report
recordsource to an expression (i.e. ="SELECT ..."&"..."&";") rather than a
literal string (i.e. SELECT ...?

Cheers

D

 

[Duane Hookom]

You must use a valid SQL syntax string which would not allow you to use
variables for field or table names.