Including null fields in crosstab columns AND rows

G

Guest

I am trying to construct a crosstab query to display product competition for
companies in a region, including reporting what cities do not have companies
selling that product, in other words, including records with no matches.
Here is how my db is set up:
Service Line Table
Product Line Table
Sub-Product Line Table
Sub-Product Lines Offered Table
Company Info Table
The relationships are:
One Service Line to Many Product Lines
One Product Line to Many Sub-Product Lines
One Sub-Product Line to Many Sub-Product Lines Offered
One CompanyID to Many Sub-Product Lines Offered
The Sub-Product Lines Offered is the "event" (related) table of the db.
I want to show *all* of the cities represented in the Company Info table as
rows in the crosstab, even if there is no related record in the Sub-Product
Lines Offered.
I want to show all of the Sub-Product Lines as the Columns in the crosstab,
even if there are no related records in the Sub-Product Lines Offered.
I want to show all of the CompanyID's in the Value section of the crosstab
(and have already successfully concatenated groupings of them, many thanks to
Duane Hookom and his patience). BUT i also want to show empty "cells" in the
crosstab if there are no matches for a City or a Sub-Product Line.
If anyone has followed this far, and is tracking with me, and has some
suggestions, I would be very grateful.
This kind of thing is very easy to do in Crystal Reports, but I'm having a
heckuva time in Access accomplishing this feat. Crystal has that excellent
"Others" grouping in its filters. In Access, you have to create queries
prior to your queries in order to include unrelated records. That's where
I'm having trouble.
Any thoughts?
TIA,
Steve Vincent
(e-mail address removed)
 
D

Duane Hookom

You might need to first create a cartesian query of all possible column and
row heading values.
SELECT DISTINCT City, SubProductLine
FROM tblCompanies, tblSubProductLines;

This should return every combination. Add the above query to your crosstab
and create joins with properties to include all of the records from the
cartesian query. Use values from the cartesian query as row and column
headings.
 
G

Guest

Duane,

On your advice, I created actually two separate cartesian queries, one for
the Cities (qryAllCities) and one for the SubProductLine (qryAllSPLs). I
changed all references to City and SubProductLine in my crosstab query, but
am now getting
Run-time error '3061':
Too few parameters. Expected 2.

This is my original, working crosstab (thank you Duane!):

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [City]=""" & [City] & """ AND
SubProductLineName=""" & [SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13) & Chr(10))) AS Company
SELECT CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery
GROUP BY CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT CARDIACINVASIVE_SubProductDataQuery.SubProductLineName;

.... and here is my "cartesian-ized" version of the crosstab, which is
getting the run-time error:

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [qryAllCities]![City]=""" &
[qryAllCities]![City] & """ AND [qryAllSPLs]![SubProductLineName]=""" &
[qryAllSPLs]![SubProductLineName] & """ ORDER BY CompanyLocationCode",Chr(13)
& Chr(10))) AS Company
SELECT qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service Line
Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery, qryAllCities, qryAllSPLs
GROUP BY qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT qryAllSPLs.SubProductLineName;

Anything sticking out as the problem?
 
D

Duane Hookom

Why create two separate cartesians? If all you data is coming from a single
table/query you might need:
====qgrpSPLN========
SELECT DISTINCT SubProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery;
and
====qgrpCity========
SELECT DISTINCT City
FROM CARDIACINVASIVE_SubProductDataQuery;
Then create your cartesian
===qcarSPLNCity========
SELECT SubProductLineName, City
FROM qgrpSPLN, qgrpCity
Then use qcarSPLNCity in your crosstab.

--
Duane Hookom
MS Access MVP

Steve Vincent said:
Duane,

On your advice, I created actually two separate cartesian queries, one for
the Cities (qryAllCities) and one for the SubProductLine (qryAllSPLs). I
changed all references to City and SubProductLine in my crosstab query,
but
am now getting
Run-time error '3061':
Too few parameters. Expected 2.

This is my original, working crosstab (thank you Duane!):

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [City]=""" & [City] & """ AND
SubProductLineName=""" & [SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13) & Chr(10))) AS Company
SELECT CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery
GROUP BY CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT CARDIACINVASIVE_SubProductDataQuery.SubProductLineName;

... and here is my "cartesian-ized" version of the crosstab, which is
getting the run-time error:

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [qryAllCities]![City]=""" &
[qryAllCities]![City] & """ AND [qryAllSPLs]![SubProductLineName]=""" &
[qryAllSPLs]![SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13)
& Chr(10))) AS Company
SELECT qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line
Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery, qryAllCities, qryAllSPLs
GROUP BY qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT qryAllSPLs.SubProductLineName;

Anything sticking out as the problem?

Duane Hookom said:
You might need to first create a cartesian query of all possible column
and
row heading values.
SELECT DISTINCT City, SubProductLine
FROM tblCompanies, tblSubProductLines;

This should return every combination. Add the above query to your
crosstab
and create joins with properties to include all of the records from the
cartesian query. Use values from the cartesian query as row and column
headings.
Click to expand...
Click to expand...
 
G

Guest

Duane, when I created the single cartesian query, it returned more than
10,000 rows in the result. I created two simpler queries in order to narrow
down the results to what I need displayed. I will try your advice -- thank
you again for the input!

Duane Hookom said:
Why create two separate cartesians? If all you data is coming from a single
table/query you might need:
====qgrpSPLN========
SELECT DISTINCT SubProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery;
and
====qgrpCity========
SELECT DISTINCT City
FROM CARDIACINVASIVE_SubProductDataQuery;
Then create your cartesian
===qcarSPLNCity========
SELECT SubProductLineName, City
FROM qgrpSPLN, qgrpCity
Then use qcarSPLNCity in your crosstab.

--
Duane Hookom
MS Access MVP

Steve Vincent said:
Duane,

On your advice, I created actually two separate cartesian queries, one for
the Cities (qryAllCities) and one for the SubProductLine (qryAllSPLs). I
changed all references to City and SubProductLine in my crosstab query,
but
am now getting
Run-time error '3061':
Too few parameters. Expected 2.

This is my original, working crosstab (thank you Duane!):

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [City]=""" & [City] & """ AND
SubProductLineName=""" & [SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13) & Chr(10))) AS Company
SELECT CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery
GROUP BY CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT CARDIACINVASIVE_SubProductDataQuery.SubProductLineName;

... and here is my "cartesian-ized" version of the crosstab, which is
getting the run-time error:

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [qryAllCities]![City]=""" &
[qryAllCities]![City] & """ AND [qryAllSPLs]![SubProductLineName]=""" &
[qryAllSPLs]![SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13)
& Chr(10))) AS Company
SELECT qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line
Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery, qryAllCities, qryAllSPLs
GROUP BY qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT qryAllSPLs.SubProductLineName;

Anything sticking out as the problem?

Duane Hookom said:
You might need to first create a cartesian query of all possible column
and
row heading values.
SELECT DISTINCT City, SubProductLine
FROM tblCompanies, tblSubProductLines;

This should return every combination. Add the above query to your
crosstab
and create joins with properties to include all of the records from the
cartesian query. Use values from the cartesian query as row and column
headings.

--
Duane Hookom
MS Access MVP

I am trying to construct a crosstab query to display product competition
for
companies in a region, including reporting what cities do not have
companies
selling that product, in other words, including records with no
matches.
Here is how my db is set up:
Service Line Table
Product Line Table
Sub-Product Line Table
Sub-Product Lines Offered Table
Company Info Table
The relationships are:
One Service Line to Many Product Lines
One Product Line to Many Sub-Product Lines
One Sub-Product Line to Many Sub-Product Lines Offered
One CompanyID to Many Sub-Product Lines Offered
The Sub-Product Lines Offered is the "event" (related) table of the db.
I want to show *all* of the cities represented in the Company Info
table
as
rows in the crosstab, even if there is no related record in the
Sub-Product
Lines Offered.
I want to show all of the Sub-Product Lines as the Columns in the
crosstab,
even if there are no related records in the Sub-Product Lines Offered.
I want to show all of the CompanyID's in the Value section of the
crosstab
(and have already successfully concatenated groupings of them, many
thanks
to
Duane Hookom and his patience). BUT i also want to show empty "cells"
in
the
crosstab if there are no matches for a City or a Sub-Product Line.
If anyone has followed this far, and is tracking with me, and has some
suggestions, I would be very grateful.
This kind of thing is very easy to do in Crystal Reports, but I'm
having a
heckuva time in Access accomplishing this feat. Crystal has that
excellent
"Others" grouping in its filters. In Access, you have to create
queries
prior to your queries in order to include unrelated records. That's
where
I'm having trouble.
Any thoughts?
TIA,
Steve Vincent
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Success! I was able to narrow down the cartesian query to 1,005 records, and
to narrow it down further in the crosstab with criteria for the rest of it.
The result is EXACTLY what I have been looking and hoping for.

I can't thank you enough for your patient and expert help.

Best,

Steve Vincent
(e-mail address removed)

Duane Hookom said:
Why create two separate cartesians? If all you data is coming from a single
table/query you might need:
====qgrpSPLN========
SELECT DISTINCT SubProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery;
and
====qgrpCity========
SELECT DISTINCT City
FROM CARDIACINVASIVE_SubProductDataQuery;
Then create your cartesian
===qcarSPLNCity========
SELECT SubProductLineName, City
FROM qgrpSPLN, qgrpCity
Then use qcarSPLNCity in your crosstab.

--
Duane Hookom
MS Access MVP

Steve Vincent said:
Duane,

On your advice, I created actually two separate cartesian queries, one for
the Cities (qryAllCities) and one for the SubProductLine (qryAllSPLs). I
changed all references to City and SubProductLine in my crosstab query,
but
am now getting
Run-time error '3061':
Too few parameters. Expected 2.

This is my original, working crosstab (thank you Duane!):

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [City]=""" & [City] & """ AND
SubProductLineName=""" & [SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13) & Chr(10))) AS Company
SELECT CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery
GROUP BY CARDIACINVASIVE_SubProductDataQuery.City,
CARDIACINVASIVE_SubProductDataQuery.[Service Line Name],
CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT CARDIACINVASIVE_SubProductDataQuery.SubProductLineName;

... and here is my "cartesian-ized" version of the crosstab, which is
getting the run-time error:

TRANSFORM First(Concatenate("Select CompanyLocationCode FROM
CARDIACINVASIVE_SubProductDataQuery WHERE [qryAllCities]![City]=""" &
[qryAllCities]![City] & """ AND [qryAllSPLs]![SubProductLineName]=""" &
[qryAllSPLs]![SubProductLineName] & """ ORDER BY
CompanyLocationCode",Chr(13)
& Chr(10))) AS Company
SELECT qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line
Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
FROM CARDIACINVASIVE_SubProductDataQuery, qryAllCities, qryAllSPLs
GROUP BY qryAllCities.City, CARDIACINVASIVE_SubProductDataQuery.[Service
Line Name], CARDIACINVASIVE_SubProductDataQuery.ProductLineName
PIVOT qryAllSPLs.SubProductLineName;

Anything sticking out as the problem?

Duane Hookom said:
You might need to first create a cartesian query of all possible column
and
row heading values.
SELECT DISTINCT City, SubProductLine
FROM tblCompanies, tblSubProductLines;

This should return every combination. Add the above query to your
crosstab
and create joins with properties to include all of the records from the
cartesian query. Use values from the cartesian query as row and column
headings.

--
Duane Hookom
MS Access MVP

I am trying to construct a crosstab query to display product competition
for
companies in a region, including reporting what cities do not have
companies
selling that product, in other words, including records with no
matches.
Here is how my db is set up:
Service Line Table
Product Line Table
Sub-Product Line Table
Sub-Product Lines Offered Table
Company Info Table
The relationships are:
One Service Line to Many Product Lines
One Product Line to Many Sub-Product Lines
One Sub-Product Line to Many Sub-Product Lines Offered
One CompanyID to Many Sub-Product Lines Offered
The Sub-Product Lines Offered is the "event" (related) table of the db.
I want to show *all* of the cities represented in the Company Info
table
as
rows in the crosstab, even if there is no related record in the
Sub-Product
Lines Offered.
I want to show all of the Sub-Product Lines as the Columns in the
crosstab,
even if there are no related records in the Sub-Product Lines Offered.
I want to show all of the CompanyID's in the Value section of the
crosstab
(and have already successfully concatenated groupings of them, many
thanks
to
Duane Hookom and his patience). BUT i also want to show empty "cells"
in
the
crosstab if there are no matches for a City or a Sub-Product Line.
If anyone has followed this far, and is tracking with me, and has some
suggestions, I would be very grateful.
This kind of thing is very easy to do in Crystal Reports, but I'm
having a
heckuva time in Access accomplishing this feat. Crystal has that
excellent
"Others" grouping in its filters. In Access, you have to create
queries
prior to your queries in order to include unrelated records. That's
where
I'm having trouble.
Any thoughts?
TIA,
Steve Vincent
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query Question 5
grouping records in a crosstab query 1
Insert a crosstab to temp table for report 6
Crosstab concatenation -- reproduce in Crystal Reports? 1
Crosstab not showing null values/fields 5
Combine crosstab with other query info 3
Displaying data across columns 1
Totalling Rows and Columns in a Crosstab Query 1

Top