Dynamic Query field changes from form values

[GuiGuy via AccessMonster.com]

All,

I have table tblTrialBalance which has fields [Account], [JanAct], [FebAct],
[MarAct], etc for Actual results as well as fields for budget and for
forecasts.
I have made a from frmFinancialSelection on which I created two option groups,
one for which month to run the report for, and another for which forecast
version to use. In my form I have created unbound fields that display the
name of the fields to use when a month is selected. Example: If user
selects February, the field name is [FebAct] for Actuals, [FebBud] for Budget,
and [FebFor] for Forecast on the current month group and [JanAct]+[FebAct]
for YTD Actuals (same with budget and forecast).

My dilema:
I want to use one report that I have created that will have the following
fields from a source query: Account, CurrentMonthActual (which is the month
selected), CurrentMonthBudget, CurrentMonthForecast, YTDActual, YTDBudget,
and YTDForecast.

However, how can I take the result from my form which is the field name in
the table [FebAct] for current month and [JanAct]+[FebAct] for YTD and input
them into the field name in the source query? Is there some other way?

 

[Duane Hookom]

In the words of fellow MVP Jeff Boyce, "you are committing spreadsheet". A
better structure would store the month in a field rather than a field name.
Can you change your structure to something like:

tblTrialBalance
==================
Account
Mth
Yr
Act
Bdgt
Forecast

or maybe even:

tblTrialBalance
==================
Account
Mth
Yr
BudgCategory (A, B, F)
Amt

 

[GuiGuy via AccessMonster.com]

One issue is that this is how the table is set up in the wonderful world of
JD Edwards and my table is linked to it via odbc.

 

[Duane Hookom]

Then you can create a union query to normalize:
SELECT [Account], 1 As Mth, [JanAct] As Act, JanBud as Bud, JanFor As For
FROM tblTrialBalance
UNION ALL

SELECT [Account], 2, [FebAct], FebBud, FebFor
FROM tblTrialBalance
UNION ALL

SELECT [Account], 3, [MarAct], MarBud, MarFor
FROM tblTrialBalance
--- etc ---
UNION ALL

SELECT [Account], 12, [DecAct], DecBud, DecFor
FROM tblTrialBalance;

You can then create flexible queries and reports.