formatting help

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

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!
 
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];
 
Alpha doesn't matter. Just trying to figure out how to get the areas across
the top.

Duane Hookom said:
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


Jennifer said:
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!
Click to expand...
Click to expand...
 
Did you try my suggestion? If so, did it work for you?

--
Duane Hookom
Microsoft Access MVP


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

Duane Hookom said:
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


Jennifer said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Jennifer said:
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
Click to expand...

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
 
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")
 
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")

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

John Spencer said:
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")

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


Jennifer said:
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?

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