Dinamic Naming in Design Grid or SQL

[John]

I have a union query that gathers financial data from a current period and a
prior period. The users uses a pick list to pick two dates. The union query
then runs the two queries and "sums" them together to get an output something
like:

current budget, prior budget, budget delta
$120 $110 $10

There are several "current-prior-delta" triplets.

My question is, in stead of naming the collumns "CBCst", "PBCst", &
"BCDelta" is there a way to name them using the format function?

Something like:

format(CurDte,"mmm-yy") & "-Budget", format(PriDgte,"mmm-yy") & "-Budget",
"BudgetDelta"

The desired result would be:

Jan-10-Budget, Dec-09-Budget, BudgetDelta

Just so I don't get chastized... Here's the Union Query:
==========
SELECT DateValue(Right(Trim([tblProjections]![period]),8)) AS CurrentPeriod,
1 As Mth, tblProjections.job, tblProjections.act, tblProjections.adesc,
tblProjections.uom, tblProjections.quan_cur, tblProjections.quan_td,
tblProjections.quan_rem, tblProjections.pc_comp, tblProjections.bdg_l_un,
tblProjections.bdg_e_un, tblProjections.bdg_m_un, tblProjections.bdg_sb_un,
tblProjections.bdg_sp_un, tblProjections.bdg_t_un, tblProjections.avgtdlun,
tblProjections.avgtdeun, tblProjections.avgtdmun, tblProjections.avgtdsbun,
tblProjections.avgtdspun, tblProjections.avgtdtun, tblProjections.ucostltc,
tblProjections.ucostetc, tblProjections.ucostmtc, tblProjections.ucostsbtc,
tblProjections.ucostsptc, tblProjections.ucostttc, tblProjections.bdg_lh_un,
tblProjections.avgtdlhun, tblProjections.ucostlhtc, tblProjections.curbdgl,
tblProjections.curbdge, tblProjections.curbdgm, tblProjections.curbdgsb,
tblProjections.curbdgsp, tblProjections.curbdgt, tblProjections.act_td_l,
tblProjections.act_td_e, tblProjections.act_td_m, tblProjections.act_td_sb,
tblProjections.act_td_sp, tblProjections.act_td_t, tblProjections.com_td_l,
tblProjections.com_td_e, tblProjections.com_td_m, tblProjections.com_td_sb,
tblProjections.com_td_sp, tblProjections.com_td_t, tblProjections.frcsttcl,
tblProjections.frcsttce, tblProjections.frcsttcm, tblProjections.frcsttcsb,
tblProjections.frcsttcsp, tblProjections.frcsttct, tblProjections.sl_prj_l,
tblProjections.sl_prj_e, tblProjections.sl_prj_m, tblProjections.sl_prj_sb,
tblProjections.sl_prj_sp, tblProjections.sl_prj_t, tblProjections.pcstmanl,
tblProjections.pcstmane, tblProjections.pcstmanm, tblProjections.pcstmansb,
tblProjections.pcstmansp, tblProjections.pcstmant, tblProjections.commflag,
tblProjections.var_l, tblProjections.var_e, tblProjections.var_m,
tblProjections.var_sb, tblProjections.var_sp, tblProjections.var_t,
tblProjections.manchgl, tblProjections.manchge, tblProjections.manchgm,
tblProjections.manchgsb, tblProjections.manchgsp, tblProjections.manchgt,
tblProjections.hrcurbudg, tblProjections.hractltd, tblProjections.hrfrcsttc,
tblProjections.hrslproj, tblProjections.hrprjwman, tblProjections.hrvar,
tblProjections.hrmanchg, tblProjections.ahrcurbdg, tblProjections.ahractltd,
tblProjections.ahrfcsttc, tblProjections.ahrslproj, tblProjections.ahrpjwman,
tblProjections.rpf, tblProjections.l, tblProjections.e, tblProjections.m,
tblProjections.sb, tblProjections.sp, tblProjections.flag,
tblProjections.audit, tblProjections.pr_key, tblProjections.sd_key
FROM tblProjections
WHERE
(((DateValue(Right(Trim([tblProjections]![period]),8)))=[forms]![frmProjectCost]![CmbCurPro]))
ORDER BY tblProjections.act, 1

UNION ALL SELECT DateValue(Right(Trim([tblProjections]![period]),8)) AS
PriorPeriod, 2 As Mth, tblProjections.job, tblProjections.act,
tblProjections.adesc, tblProjections.uom, tblProjections.quan_cur,
tblProjections.quan_td, tblProjections.quan_rem, tblProjections.pc_comp,
tblProjections.bdg_l_un, tblProjections.bdg_e_un, tblProjections.bdg_m_un,
tblProjections.bdg_sb_un, tblProjections.bdg_sp_un, tblProjections.bdg_t_un,
tblProjections.avgtdlun, tblProjections.avgtdeun, tblProjections.avgtdmun,
tblProjections.avgtdsbun, tblProjections.avgtdspun, tblProjections.avgtdtun,
tblProjections.ucostltc, tblProjections.ucostetc, tblProjections.ucostmtc,
tblProjections.ucostsbtc, tblProjections.ucostsptc, tblProjections.ucostttc,
tblProjections.bdg_lh_un, tblProjections.avgtdlhun, tblProjections.ucostlhtc,
tblProjections.curbdgl, tblProjections.curbdge, tblProjections.curbdgm,
tblProjections.curbdgsb, tblProjections.curbdgsp, tblProjections.curbdgt,
tblProjections.act_td_l, tblProjections.act_td_e, tblProjections.act_td_m,
tblProjections.act_td_sb, tblProjections.act_td_sp, tblProjections.act_td_t,
tblProjections.com_td_l, tblProjections.com_td_e, tblProjections.com_td_m,
tblProjections.com_td_sb, tblProjections.com_td_sp, tblProjections.com_td_t,
tblProjections.frcsttcl, tblProjections.frcsttce, tblProjections.frcsttcm,
tblProjections.frcsttcsb, tblProjections.frcsttcsp, tblProjections.frcsttct,
tblProjections.sl_prj_l, tblProjections.sl_prj_e, tblProjections.sl_prj_m,
tblProjections.sl_prj_sb, tblProjections.sl_prj_sp, tblProjections.sl_prj_t,
tblProjections.pcstmanl, tblProjections.pcstmane, tblProjections.pcstmanm,
tblProjections.pcstmansb, tblProjections.pcstmansp, tblProjections.pcstmant,
tblProjections.commflag, tblProjections.var_l, tblProjections.var_e,
tblProjections.var_m, tblProjections.var_sb, tblProjections.var_sp,
tblProjections.var_t, tblProjections.manchgl, tblProjections.manchge,
tblProjections.manchgm, tblProjections.manchgsb, tblProjections.manchgsp,
tblProjections.manchgt, tblProjections.hrcurbudg, tblProjections.hractltd,
tblProjections.hrfrcsttc, tblProjections.hrslproj, tblProjections.hrprjwman,
tblProjections.hrvar, tblProjections.hrmanchg, tblProjections.ahrcurbdg,
tblProjections.ahractltd, tblProjections.ahrfcsttc, tblProjections.ahrslproj,
tblProjections.ahrpjwman, tblProjections.rpf, tblProjections.l,
tblProjections.e, tblProjections.m, tblProjections.sb, tblProjections.sp,
tblProjections.flag, tblProjections.audit, tblProjections.pr_key,
tblProjections.sd_key
FROM tblProjections
WHERE
(((DateValue(Right(Trim([tblProjections]![period]),8)))=[forms]![frmProjectCost]![CmbPriPro]))
ORDER BY tblProjections.act, 2;
==========

This query is used in another query to sum by the Mth (1 or 2) to get the
two values to calculate the difference.

 

[PieterLinden via AccessMonster.com]

I have a union query that gathers financial data from a current period and a
prior period. The users uses a pick list to pick two dates. The union query
then runs the two queries and "sums" them together to get an output something
like:

current budget, prior budget, budget delta
$120 $110 $10

There are several "current-prior-delta" triplets.

My question is, in stead of naming the collumns "CBCst", "PBCst", &
"BCDelta" is there a way to name them using the format function?

In a word, no. The only way to rename a column is to use AS...

SELECT CBCst AS 'Current Budget', PBCst AS 'Prior Budget', BCDelta AS 'Budget
Delta'
FROM....

if you to this in the first select statement in your union query, you'll get
the naming okay. As for doing this on the fly - no can do... Only way to do
that is to modify the QueryDef's SQL property (basically using VBA under the
covers.)

 

[John]

Thanks for the feedback... I was afraid that would be the answer...