formatting help

[Jennifer]

I have a query that is setup like this:

Manager Name: Area
Jennifer Atlanta
Jennifer Boston
Jennifer Chicago
Debbie San Franciso
Debbie Los Angeles
Debbie Detroit

And I want a report to read like this:

Jennifer Atlanta Boston Chicago
Debbie San Francisco Los Angeles Detroit

I am at a loss as how to get it set up. Any help is much appreciated!

 

[Duane Hookom]

Can you tell us why the areas are in alpha order for Jennifer but reversed
for Debbie? Is this necessary?

You can try first create a ranking query:

=== qryJenniferRanking ===
SELECT qryJennifer.[Manager Name], qryJennifer.Area,
Count(qryJennifer_1.[Manager Name]) AS [CountOfManager Name]
FROM qryJennifer AS qryJennifer_1 INNER JOIN qryJennifer ON
qryJennifer_1.[Manager Name] = qryJennifer.[Manager Name]
WHERE (((qryJennifer_1.Area)<=[qryJennifer].[Area]))
GROUP BY qryJennifer.[Manager Name], qryJennifer.Area;

Then create a crosstab from the result
=== qxtbJennifer ===
TRANSFORM First(qryJenniferRanking.Area) AS FirstOfArea
SELECT qryJenniferRanking.[Manager Name]
FROM qryJenniferRanking
GROUP BY qryJenniferRanking.[Manager Name]
PIVOT "Area" & [CountOfManager Name];

 

[Jennifer]

Alpha doesn't matter. Just trying to figure out how to get the areas across
the top.

Can you tell us why the areas are in alpha order for Jennifer but reversed
for Debbie? Is this necessary?

You can try first create a ranking query:

=== qryJenniferRanking ===
SELECT qryJennifer.[Manager Name], qryJennifer.Area,
Count(qryJennifer_1.[Manager Name]) AS [CountOfManager Name]
FROM qryJennifer AS qryJennifer_1 INNER JOIN qryJennifer ON
qryJennifer_1.[Manager Name] = qryJennifer.[Manager Name]
WHERE (((qryJennifer_1.Area)<=[qryJennifer].[Area]))
GROUP BY qryJennifer.[Manager Name], qryJennifer.Area;

Then create a crosstab from the result
=== qxtbJennifer ===
TRANSFORM First(qryJenniferRanking.Area) AS FirstOfArea
SELECT qryJenniferRanking.[Manager Name]
FROM qryJenniferRanking
GROUP BY qryJenniferRanking.[Manager Name]
PIVOT "Area" & [CountOfManager Name];

--
Duane Hookom
Microsoft Access MVP

I have a query that is setup like this:

Manager Name: Area
Jennifer Atlanta
Jennifer Boston
Jennifer Chicago
Debbie San Franciso
Debbie Los Angeles
Debbie Detroit

And I want a report to read like this:

Jennifer Atlanta Boston Chicago
Debbie San Francisco Los Angeles Detroit

I am at a loss as how to get it set up. Any help is much appreciated!

 

[Duane Hookom]

Did you try my suggestion? If so, did it work for you?

--
Duane Hookom
Microsoft Access MVP

Alpha doesn't matter. Just trying to figure out how to get the areas across
the top.

Can you tell us why the areas are in alpha order for Jennifer but reversed
for Debbie? Is this necessary?

You can try first create a ranking query:

=== qryJenniferRanking ===
SELECT qryJennifer.[Manager Name], qryJennifer.Area,
Count(qryJennifer_1.[Manager Name]) AS [CountOfManager Name]
FROM qryJennifer AS qryJennifer_1 INNER JOIN qryJennifer ON
qryJennifer_1.[Manager Name] = qryJennifer.[Manager Name]
WHERE (((qryJennifer_1.Area)<=[qryJennifer].[Area]))
GROUP BY qryJennifer.[Manager Name], qryJennifer.Area;

Then create a crosstab from the result
=== qxtbJennifer ===
TRANSFORM First(qryJenniferRanking.Area) AS FirstOfArea
SELECT qryJenniferRanking.[Manager Name]
FROM qryJenniferRanking
GROUP BY qryJenniferRanking.[Manager Name]
PIVOT "Area" & [CountOfManager Name];

--
Duane Hookom
Microsoft Access MVP

I have a query that is setup like this:

Manager Name: Area
Jennifer Atlanta
Jennifer Boston
Jennifer Chicago
Debbie San Franciso
Debbie Los Angeles
Debbie Detroit

And I want a report to read like this:

Jennifer Atlanta Boston Chicago
Debbie San Francisco Los Angeles Detroit

I am at a loss as how to get it set up. Any help is much appreciated!

 

[Marshall Barton]

I have a query that is setup like this:

Manager Name: Area
Jennifer Atlanta
Jennifer Boston
Jennifer Chicago
Debbie San Franciso
Debbie Los Angeles
Debbie Detroit

And I want a report to read like this:

Jennifer Atlanta Boston Chicago
Debbie San Francisco Los Angeles Detroit

You could use the Concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16&SID=ab8b63665832173za638e96ea83279fb
if you wanted the output to look like:

Jennifer Atlanta, Boston, Chicago
Debbie San Francisco, Los Angeles, Detroit

 

[Jennifer]

I'm using this function but getting an error: Undefined function
"concatenate" in expression??

Division: Concatenate("SELECT DivisionCode FROM qryAdminListing WHERE
AdminID =" & [AdminID] & " ORDER BY ManagerName")

 

[John Spencer]

Did you download the sample database?

Did you copy the VBA function Concatenate from the sample and add it to your
database in a VBA module?

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

I'm using this function but getting an error: Undefined function
"concatenate" in expression??

Division: Concatenate("SELECT DivisionCode FROM qryAdminListing WHERE
AdminID =" & [AdminID] & " ORDER BY ManagerName")

You could use the Concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16&SID=ab8b63665832173za638e96ea83279fb
if you wanted the output to look like:

Jennifer Atlanta, Boston, Chicago
Debbie San Francisco, Los Angeles, Detroit

 

[Jennifer]

yes I did but still getting the error message. I have tried to "simplify"
the tables and nothing seems to work. I have access 2003, does that matter?

Did you download the sample database?

Did you copy the VBA function Concatenate from the sample and add it to your
database in a VBA module?

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

I'm using this function but getting an error: Undefined function
"concatenate" in expression??

Division: Concatenate("SELECT DivisionCode FROM qryAdminListing WHERE
AdminID =" & [AdminID] & " ORDER BY ManagerName")

Jennifer wrote:

I have a query that is setup like this:

Manager Name: Area
Jennifer Atlanta
Jennifer Boston
Jennifer Chicago
Debbie San Franciso
Debbie Los Angeles
Debbie Detroit

And I want a report to read like this:

Jennifer Atlanta Boston Chicago
Debbie San Francisco Los Angeles Detroit

You could use the Concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16&SID=ab8b63665832173za638e96ea83279fb
if you wanted the output to look like:

Jennifer Atlanta, Boston, Chicago
Debbie San Francisco, Los Angeles, Detroit

 

[Duane Hookom]

Since you suggested you copied the function into a standard module, did you
save the module with a name other than "Concatenate"? A function and a module
should never have the same name or you will get errors.
--
Duane Hookom
Microsoft Access MVP

yes I did but still getting the error message. I have tried to "simplify"
the tables and nothing seems to work. I have access 2003, does that matter?

Did you download the sample database?

Did you copy the VBA function Concatenate from the sample and add it to your
database in a VBA module?

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

I'm using this function but getting an error: Undefined function
"concatenate" in expression??

Division: Concatenate("SELECT DivisionCode FROM qryAdminListing WHERE
AdminID =" & [AdminID] & " ORDER BY ManagerName")

:

Jennifer wrote:

I have a query that is setup like this:

Manager Name: Area
Jennifer Atlanta
Jennifer Boston
Jennifer Chicago
Debbie San Franciso
Debbie Los Angeles
Debbie Detroit

And I want a report to read like this:

Jennifer Atlanta Boston Chicago
Debbie San Francisco Los Angeles Detroit

You could use the Concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16&SID=ab8b63665832173za638e96ea83279fb
if you wanted the output to look like:

Jennifer Atlanta, Boston, Chicago
Debbie San Francisco, Los Angeles, Detroit