Can TRANSFORM be used in a Crosstab Query to show text

  • Thread starter Thread starter skholm
  • Start date Start date
S

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
 
Use MIN, MAX, FIRST or LAST as aggregate, instead of COUNT or SUM.

Vanderghast, Access 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
 
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

John Spencer MVP said:
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
Click to expand...
Click to expand...
 
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


skholm said:
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

John Spencer MVP said:
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
Click to expand...
Click to expand...
Click to expand...
 
Duane,
Can you post an example of how to use your Concatenate function in a crosstab query? I see how to use it in a normal query, but I'm getting confusing error messages when I try to plug it into the crosstab.

If using the example above, I tried:
TRANSFORM Concatenate(tblEDACSSite.[Site Name]) AS ...
and
TRANSFORM Concatenate(select site_name from tblEDACSSite) AS ...

Thanks!
 
Back
Top