How to get ALL product lines to show in a summary report

[Phil]

If a crosstab report comes up with a row that does not any matching data
for that particular coloumn, you get a blank space in that area, ora zero

If you are working with a regular select query, and there is no matching
data for that row, you don't get a row.

I have a report with a particular key field, (product line), and I want
to be able to run a select query for a report (sales by customer for
instance,) that will show a line for each product line, even if a
customer has not made any sales for that product line. Normally if
customer A had no sales in bicycles, I would not get a line for
bicycles. I want a line for each product line, regardless of whether
there is any data that fits or not. I want the list of items to come
from the data, IE a unique list of [ProductLine].

Easiest way to do this?

Phil

 

[Duane Hookom]

Create a new query based on your crosstab query and a table with every
product line. Create a join line that includes all records from the product
line table.

 

[Phil]

I thought of something like that. I created a query of unique
[productline], then joined it so that all uniques would select even if
no matching entry in the sales tables. It failed.

 

[Phil]

I tried something like that, and it did not work. Here is my original
summary select query. It gives me sums of cost for each customer by
itemtype.


SELECT [Unionized Data Table].customer_id, customer.name, [Unionized
Data Table].item_types_name, [Unionized Data Table].cost
FROM customer RIGHT JOIN [Unionized Data Table] ON customer.customer_id
= [Unionized Data Table].[Customer# AS #]
GROUP BY [Unionized Data Table].customer_id, customer.name, [Unionized
Data Table].item_types_name
ORDER BY [Unionized Data Table].customer_id, customer.name;


I have a query called TYPES with a single field called TYPES. This
creates a unique list of item types (30 in total), and I want my final
select query to give me 30 lines per customer, whether or not a customer
ordered anything on any lines,. The ultimate test is a customer with NO
sales at all, which should still give me 30 lines. I tried to join it
like this:


SELECT [Unionized Data Table].customer_id, TYPES.TYPES, customer.name,
[Unionized Data Table].item_types_name, Sum([Unionized Data Table].cost)
AS SumOfcost
FROM TYPES LEFT JOIN (customer RIGHT JOIN [Unionized Data Table] ON
customer.customer_id = [Unionized Data Table].[Customer# AS #]) ON
TYPES.TYPES = [Unionized Data Table].item_types_name
GROUP BY [Unionized Data Table].customer_id, TYPES.TYPES, customer.name,
[Unionized Data Table].item_types_name
ORDER BY [Unionized Data Table].customer_id, TYPES.TYPES, customer.name;


And it does not work. I understand why it does not work, but can't
figure out how to join it in a way that WILL work.

 

[Phil]

Since I want this report of items types by customer, my 'placeholder'
query will need to be every item FOR every customer, right?

 

[Duane Hookom]

Create a query based on unique types and customers. This would be a
cartesian query since there are no join lines. Use this query to join to
your crosstab results.

--
Duane Hookom
MS Access MVP
--

I tried something like that, and it did not work. Here is my original
summary select query. It gives me sums of cost for each customer by
itemtype.


SELECT [Unionized Data Table].customer_id, customer.name, [Unionized Data
Table].item_types_name, [Unionized Data Table].cost
FROM customer RIGHT JOIN [Unionized Data Table] ON customer.customer_id =
[Unionized Data Table].[Customer# AS #]
GROUP BY [Unionized Data Table].customer_id, customer.name, [Unionized
Data Table].item_types_name
ORDER BY [Unionized Data Table].customer_id, customer.name;


I have a query called TYPES with a single field called TYPES. This
creates a unique list of item types (30 in total), and I want my final
select query to give me 30 lines per customer, whether or not a customer
ordered anything on any lines,. The ultimate test is a customer with NO
sales at all, which should still give me 30 lines. I tried to join it
like this:


SELECT [Unionized Data Table].customer_id, TYPES.TYPES, customer.name,
[Unionized Data Table].item_types_name, Sum([Unionized Data Table].cost)
AS SumOfcost
FROM TYPES LEFT JOIN (customer RIGHT JOIN [Unionized Data Table] ON
customer.customer_id = [Unionized Data Table].[Customer# AS #]) ON
TYPES.TYPES = [Unionized Data Table].item_types_name
GROUP BY [Unionized Data Table].customer_id, TYPES.TYPES, customer.name,
[Unionized Data Table].item_types_name
ORDER BY [Unionized Data Table].customer_id, TYPES.TYPES, customer.name;


And it does not work. I understand why it does not work, but can't figure
out how to join it in a way that WILL work.

Create a new query based on your crosstab query and a table with every
product line. Create a join line that includes all records from the
product line table.