If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
an ALIAS question?
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 |
#2
|
|||
|
|||
an ALIAS question?
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] |
#3
|
|||
|
|||
an ALIAS question?
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/f...cz2 667z7b7b9. -- Duane Hookom MS Access MVP "cinnie" wrote in message ... 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 |
#4
|
|||
|
|||
an ALIAS question?
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] . |
Thread Tools | |
Display Modes | |
|
|