Dynamic Query field changes from form values

  • Thread starter Thread starter GuiGuy via AccessMonster.com
  • Start date Start date
G

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?
 
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
 
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.
 
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.
 
Back
Top