Can TRANSFORM be used in a Crosstab Query to show text

[skholm]

I would like to be able to make a 'matrix' with the intersection of the Row
(System) & Column (Color) to show the text content(s) of the field (Site
Name(s)). If the content is a unique number I have used the TRANSFORM
Sum(...) to display the infomation but now I would like to display a text
value.
Example:
TRANSFORM Count(tblEDACSSite.[Site Name]) AS [CountOfSite Name]
SELECT tblRACOMSystems.[System Name], tblRACOMSystems.Set
FROM tblSiteColorCode INNER JOIN (tblHomeSite INNER JOIN (tblRACOMSystems
INNER JOIN tblEDACSSite ON tblRACOMSystems.[System Name] =
tblEDACSSite.SystemPreRebanding) ON tblHomeSite.[H-Site] =
tblEDACSSite.[H-Site]) ON tblSiteColorCode.ColorCode = tblHomeSite.ColorCode
GROUP BY tblRACOMSystems.[System Name], tblRACOMSystems.Set
PIVOT tblSiteColorCode.ColorCode;
TRANSFORM Count(...) shows the number of Sites but I would like to display
the Sites Name(s). Is there an expression that will do this?
Regards - Steve

 

[Michel Walsh]

Use MIN, MAX, FIRST or LAST as aggregate, instead of COUNT or SUM.

Vanderghast, Access MVP

 

[John Spencer MVP]

Instead of COUNT use First, Last, Min, or Max. Those will all return a text
value for Site Name. The problem would be that they won't necessarily return
all the sites involved.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[skholm]

That work great but you are correct, it will only return one site. Too bad
there isn't an 'All' aggregate... - Thanks for the help - Regards, Steve

Instead of COUNT use First, Last, Min, or Max. Those will all return a text
value for Site Name. The problem would be that they won't necessarily return
all the sites involved.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I would like to be able to make a 'matrix' with the intersection of the Row
(System) & Column (Color) to show the text content(s) of the field (Site
Name(s)). If the content is a unique number I have used the TRANSFORM
Sum(...) to display the infomation but now I would like to display a text
value.
Example:
TRANSFORM Count(tblEDACSSite.[Site Name]) AS [CountOfSite Name]
SELECT tblRACOMSystems.[System Name], tblRACOMSystems.Set
FROM tblSiteColorCode INNER JOIN (tblHomeSite INNER JOIN (tblRACOMSystems
INNER JOIN tblEDACSSite ON tblRACOMSystems.[System Name] =
tblEDACSSite.SystemPreRebanding) ON tblHomeSite.[H-Site] =
tblEDACSSite.[H-Site]) ON tblSiteColorCode.ColorCode = tblHomeSite.ColorCode
GROUP BY tblRACOMSystems.[System Name], tblRACOMSystems.Set
PIVOT tblSiteColorCode.ColorCode;
TRANSFORM Count(...) shows the number of Sites but I would like to display
the Sites Name(s). Is there an expression that will do this?
Regards - Steve

 

[Duane Hookom]

You can get all using the generic concatenate function found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=z3d4c9e2f845b251be76a44785c9c273
--
Duane Hookom
Microsoft Access MVP

That work great but you are correct, it will only return one site. Too bad
there isn't an 'All' aggregate... - Thanks for the help - Regards, Steve

Instead of COUNT use First, Last, Min, or Max. Those will all return a text
value for Site Name. The problem would be that they won't necessarily return
all the sites involved.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I would like to be able to make a 'matrix' with the intersection of the Row
(System) & Column (Color) to show the text content(s) of the field (Site
Name(s)). If the content is a unique number I have used the TRANSFORM
Sum(...) to display the infomation but now I would like to display a text
value.
Example:
TRANSFORM Count(tblEDACSSite.[Site Name]) AS [CountOfSite Name]
SELECT tblRACOMSystems.[System Name], tblRACOMSystems.Set
FROM tblSiteColorCode INNER JOIN (tblHomeSite INNER JOIN (tblRACOMSystems
INNER JOIN tblEDACSSite ON tblRACOMSystems.[System Name] =
tblEDACSSite.SystemPreRebanding) ON tblHomeSite.[H-Site] =
tblEDACSSite.[H-Site]) ON tblSiteColorCode.ColorCode = tblHomeSite.ColorCode
GROUP BY tblRACOMSystems.[System Name], tblRACOMSystems.Set
PIVOT tblSiteColorCode.ColorCode;
TRANSFORM Count(...) shows the number of Sites but I would like to display
the Sites Name(s). Is there an expression that will do this?
Regards - Steve