Last one

  • Thread starter Thread starter grantschneider
  • Start date Start date
G

grantschneider

I am having trouble figuring out how to show columns and rows in cross
tab reports that dont have any data within the table. I am not sure
this makes sense but for example if I am making a crosstab report with
the colums being the sales teams and the rows being the industry and
the values being the number of sales, if there are industries or sales
teams which have no sales, how do i get them to show up in the
crosstab query (or eventually, in the report)
 
In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

You might also check out Duane Hookom's example at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Cross Tab'
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I realized that I only answered one part of your question. The columns
part.

The rows part would require you to have a table of industries that you could
outer join to the unnamed table that contains your data.

Add the industries table to the query and join it to your other table
Now double click on the join line and select the option that shows all for
the industries table and only matching for the other table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Here is my SQL code:

TRANSFORM Nz(Count([Lead Source Analysis].[1st Sale]),0) AS
[CountOf1st Sale]
SELECT [Lead Source Analysis].Industry, Count([Lead Source Analysis].
[1st Sale]) AS [Total Of 1st Sale]
FROM [Lead Source Analysis]
GROUP BY [Lead Source Analysis].Industry
PIVOT [Lead Source Analysis].Team;

I need a way to make the colums with all zero values show up as well
as rows.
 
Here is my SQL code:

TRANSFORM Nz(Count([Lead Source Analysis].[1st Sale]),0) AS
[CountOf1st Sale]
SELECT [Lead Source Analysis].Industry, Count([Lead Source Analysis].
[1st Sale]) AS [Total Of 1st Sale]
FROM [Lead Source Analysis]
GROUP BY [Lead Source Analysis].Industry
PIVOT [Lead Source Analysis].Team;

I need a way to make the colums with all zero values show up as well
as rows.
Click to expand...


I did it with the columns, now I can't figure out the rows.
 
I realized that I only answered one part of your question. The columns
part.

The rows part would require you to have a table of industries that you could
outer join to the unnamed table that contains your data.

Add the industries table to the query and join it to your other table
Now double click on the join line and select the option that shows all for
the industries table and only matching for the other table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.











- Show quoted text -
Click to expand...

I have a table of Industries and linked it correctly to the table.
When I run the query it adds the correct number of rows but they all
happen to be blank. Any ideas?

Thank you,
Grant
 
I have a table of Industries and linked it correctly to the table.
When I run the query it adds the correct number of rows but they all
happen to be blank. Any ideas?

Thank you,
Grant
Click to expand...

SOMETHING like the following.

TRANSFORM CLng(Nz(Count([Lead Source Analysis].[1st Sale]),0)) AS
[CountOf1st Sale]
SELECT INDUSTRIES.Industry
, Count([Lead Source Analysis].[1st Sale]) AS [Total Of 1st Sale]
FROM INDUSTRIES LEFT JOIN [Lead Source Analysis]
ON INDUSTRIES.Industry = [Lead Source Analysis].Industry
GROUP BY INDUSTRIES.Industry
PIVOT [Lead Source Analysis].Team IN (The list of Teams)

Note that I changed the SELECT and GROUP By clauses to use
INDUSTRIES.Industry. If your query still gives you the "wrong" results,
please post the SQL of the query.
 
I have a table of Industries and linked it correctly to the table.
When I run the query it adds the correct number of rows but they all
happen to be blank. Any ideas?
Click to expand...
Thank you,
Grant
Click to expand...

SOMETHING like the following.

TRANSFORM CLng(Nz(Count([Lead Source Analysis].[1st Sale]),0)) AS
[CountOf1st Sale]
SELECT INDUSTRIES.Industry
, Count([Lead Source Analysis].[1st Sale]) AS [Total Of 1st Sale]
FROM INDUSTRIES LEFT JOIN [Lead Source Analysis]
ON INDUSTRIES.Industry = [Lead Source Analysis].Industry
GROUP BY INDUSTRIES.Industry
PIVOT [Lead Source Analysis].Team IN (The list of Teams)

Note that I changed the SELECT and GROUP By clauses to use
INDUSTRIES.Industry. If your query still gives you the "wrong" results,
please post the SQL of the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County- Hide quoted text -

- Show quoted text -
Click to expand...

I tried using your copy and changed the relevant parts and it didnt
seem to work.
This is my SQL:

TRANSFORM Nz(Sum([Lead Source Analysis].Share),0) AS SumOfShare
SELECT [Lead Source Analysis].Industry, Sum([Lead Source
Analysis].Share) AS [Total Of Share]
FROM [Form Lists] LEFT JOIN [Lead Source Analysis] ON [Form
Lists].Industry=[Lead Source Analysis].Industry
GROUP BY [Lead Source Analysis].Industry, [Form Lists].Industry
PIVOT [Lead Source Analysis].Team In
("1","2","3","4","5","6","7","8","9","10","11","12","13");
 
NOTE that you are still referring to the wrong table in the select and group
by clauses if you want the Industry line to show anything but null

TRANSFORM Nz(Sum([Lead Source Analysis].Share),0) AS SumOfShare

SELECT [FORM LISTS].Industry <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

, Sum([Lead SourceAnalysis].Share) AS [Total Of Share]
FROM [Form Lists] LEFT JOIN [Lead Source Analysis]
ON [Form Lists].Industry=[Lead Source Analysis].Industry

GROUP BY [FORM LISTS].Industry <<<<<<<<<<<<<<<<<<<<<<<<<<<<<

PIVOT [Lead Source Analysis].Team In
("1","2","3","4","5","6","7","8","9","10","11","12","13");


If that doesn't work tell us how it fails. Syntax error, wrong results,
etc. If wrong results, what is wrong with the results - please be specific.
You can see what happens, we can't and are dependent on your description to
figure out what is wrong.

(By the way remove the <<<<<<< from the above - I know you know this, but
I have had people leave stuff in like that.)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I realized that I only answered one part of your question. The
columns
part.
Click to expand...
The rows part would require you to have a table of industries that you
could
outer join to the unnamed table that contains your data.
Click to expand...
Add the industries table to the query and join it to your other table
Now double click on the join line and select the option that shows all
for
the industries table and only matching for the other table.
Click to expand...
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
news:[email protected]...
Click to expand...
I have a table of Industries and linked it correctly to the table.
When I run the query it adds the correct number of rows but they all
happen to be blank. Any ideas?
Click to expand...
Thank you,
Grant
Click to expand...

SOMETHING like the following.

TRANSFORM CLng(Nz(Count([Lead Source Analysis].[1st Sale]),0)) AS
[CountOf1st Sale]
SELECT INDUSTRIES.Industry
, Count([Lead Source Analysis].[1st Sale]) AS [Total Of 1st Sale]
FROM INDUSTRIES LEFT JOIN [Lead Source Analysis]
ON INDUSTRIES.Industry = [Lead Source Analysis].Industry
GROUP BY INDUSTRIES.Industry
PIVOT [Lead Source Analysis].Team IN (The list of Teams)

Note that I changed the SELECT and GROUP By clauses to use
INDUSTRIES.Industry. If your query still gives you the "wrong" results,
please post the SQL of the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County- Hide quoted text -

- Show quoted text -
Click to expand...

I tried using your copy and changed the relevant parts and it didnt
seem to work.
This is my SQL:

TRANSFORM Nz(Sum([Lead Source Analysis].Share),0) AS SumOfShare
SELECT [Lead Source Analysis].Industry, Sum([Lead Source
Analysis].Share) AS [Total Of Share]
FROM [Form Lists] LEFT JOIN [Lead Source Analysis] ON [Form
Lists].Industry=[Lead Source Analysis].Industry
GROUP BY [Lead Source Analysis].Industry, [Form Lists].Industry
PIVOT [Lead Source Analysis].Team In
("1","2","3","4","5","6","7","8","9","10","11","12","13");
Click to expand...
 
On Jul 10, 1:10 pm, "John Spencer" <[email protected]> wrote:
Click to expand...

I have a table of Industries and linked it correctly to the table.
When I run the query it adds the correct number of rows but they all
happen to be blank. Any ideas?

Thank you,
Grant
Click to expand...

Grant Schneider,

Please provide:

1 -- The relevant portions of the table structures, including column names and data types,
and all keys and indexes.

2 -- Relevant sample data from the tables.

3 -- A list of your desired results.

4 -- SQL attempted to date.


Sincerely,

Chris O.
 
Back
Top