an ALIAS question?
in [Queries]
|
From: cinnie on 21 Mar 2010 16:50 hello gurus I have a Report based on the Cross Tab query shown below: TRANSFORM Count(qryEmp.SiteID) AS CountOfSiteID SELECT qryEmp.SiteName, Count(qryEmp.SiteID) AS [Site Count] FROM qryEmp GROUP BY qryEmp.SiteName PIVOT qryEmp.MtgCode; Each record in the detail section of the report shows SiteName, [Site Count], and up to 10 more fields, one for each of the values of MtgCode. Typical values for MtgCode might be York1, York2, D1, Essex-05 ... Here is my problem. Because these values change all the time, I want the text boxex in the Report's detail section to refer to Control Sources of A, B, C... instead York1, York2, D1.... . Also, space constraints dictate this A,B,C... scheme. This sounds like an ideal place to use ALIAS, but I can't figure out how to assign ALIASes to the pivot fields. Hope this makes sense! -- cinnie
From: Marshall Barton on 21 Mar 2010 18:02 cinnie wrote: >I have a Report based on the Cross Tab query shown below: > > TRANSFORM Count(qryEmp.SiteID) AS CountOfSiteID > SELECT qryEmp.SiteName, Count(qryEmp.SiteID) AS [Site Count] > FROM qryEmp > GROUP BY qryEmp.SiteName > PIVOT qryEmp.MtgCode; > >Each record in the detail section of the report shows SiteName, [Site >Count], and up to 10 more fields, one for each of the values of MtgCode. >Typical values for MtgCode might be York1, York2, D1, Essex-05 ... > >Here is my problem. Because these values change all the time, I want the >text boxex in the Report's detail section to refer to Control Sources of A, >B, C... instead York1, York2, D1.... . Also, space constraints dictate this >A,B,C... scheme. > >This sounds like an ideal place to use ALIAS, but I can't figure out how to >assign ALIASes to the pivot fields. You can't. A crosstab query calculates the field names from the contents of the Pivot field in the selected records. If you want to use something other than MtgCode, then you need another field in the table that contains the aliases (A, B, C...) that you want to see in the query. If you can't change the table you have now, then create another table with just fields for the MtgCode and its alias and join it in your query. -- Marsh MVP [MS Access]
From: Duane Hookom on 21 Mar 2010 21:44 Great idea to provide an alias for each column. This is the exact solution used in the crosstab report demo at http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11&SID=b45da9f9f41z2f73182ecz2667z7b7b9. -- Duane Hookom MS Access MVP "cinnie" <cinnie(a)discussions.microsoft.com> wrote in message news:5550A04C-19C0-4255-A4BC-EA4605522CD9(a)microsoft.com... > hello gurus > > I have a Report based on the Cross Tab query shown below: > > TRANSFORM Count(qryEmp.SiteID) AS CountOfSiteID > SELECT qryEmp.SiteName, Count(qryEmp.SiteID) AS [Site Count] > FROM qryEmp > GROUP BY qryEmp.SiteName > PIVOT qryEmp.MtgCode; > > Each record in the detail section of the report shows SiteName, [Site > Count], and up to 10 more fields, one for each of the values of MtgCode. > Typical values for MtgCode might be York1, York2, D1, Essex-05 ... > > Here is my problem. Because these values change all the time, I want the > text boxex in the Report's detail section to refer to Control Sources of > A, > B, C... instead York1, York2, D1.... . Also, space constraints dictate > this > A,B,C... scheme. > > This sounds like an ideal place to use ALIAS, but I can't figure out how > to > assign ALIASes to the pivot fields. Hope this makes sense! > > -- > cinnie
From: cinnie on 22 Mar 2010 12:41 thanks to Marshall and Duane for some solid advice - cinnie -- cinnie "Marshall Barton" wrote: > cinnie wrote: > >I have a Report based on the Cross Tab query shown below: > > > > TRANSFORM Count(qryEmp.SiteID) AS CountOfSiteID > > SELECT qryEmp.SiteName, Count(qryEmp.SiteID) AS [Site Count] > > FROM qryEmp > > GROUP BY qryEmp.SiteName > > PIVOT qryEmp.MtgCode; > > > >Each record in the detail section of the report shows SiteName, [Site > >Count], and up to 10 more fields, one for each of the values of MtgCode. > >Typical values for MtgCode might be York1, York2, D1, Essex-05 ... > > > >Here is my problem. Because these values change all the time, I want the > >text boxex in the Report's detail section to refer to Control Sources of A, > >B, C... instead York1, York2, D1.... . Also, space constraints dictate this > >A,B,C... scheme. > > > >This sounds like an ideal place to use ALIAS, but I can't figure out how to > >assign ALIASes to the pivot fields. > > > You can't. A crosstab query calculates the field names from > the contents of the Pivot field in the selected records. If > you want to use something other than MtgCode, then you need > another field in the table that contains the aliases (A, B, > C...) that you want to see in the query. If you can't > change the table you have now, then create another table > with just fields for the MtgCode and its alias and join it > in your query. > > -- > Marsh > MVP [MS Access] > . >
|
Pages: 1 Prev: parameter query issue with single date Next: Update Query |