an ALIAS question?

  • Thread starter Thread starter cinnie
  • Start date Start date
C

cinnie

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!
 
thanks to Marshall and Duane for some solid advice - cinnie
--
cinnie


Marshall Barton said:
cinnie said:
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.
Click to expand...


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.
Click to expand...
 
Back
Top