Dinamic Naming in Design Grid or SQL
in [Queries]
|
From: John on 22 Feb 2010 16:43 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. -- Thanks in advance! **John**
From: PieterLinden via AccessMonster.com on 1 Mar 2010 02:59 John wrote: >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.) -- Message posted via http://www.accessmonster.com
From: John on 1 Mar 2010 09:06 Thanks for the feedback... I was afraid that would be the answer... -- Thanks in advance! **John** "PieterLinden via AccessMonster.com" wrote: > John wrote: > >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.) > > -- > Message posted via http://www.accessmonster.com > > . >
|
Pages: 1 Prev: Outlook 2007 and problems with RSS Feeds Next: Append query with username and date |