J
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.
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.