Query To Add Empty Records To Dataset?

[kestefon]

Hi,


I have a dataset showing sales by salesperson for each product. I'd like to summarize this data in a table that looks like this:




Note that some salespeople don't sell certain products and that these records show up with "0". My problem is that, in my actual dataset, if a salesperson doesn't sell a product, that records doesn't exist at all. Is there any way to append empty "0" records to my dataset? See attached for an Access file.


DatasetProblem.accdb

[June7]

This will require a dataset of every possible salesperson/product combination. That dataset can be generated with query that includes those two tables WITHOUT a join. This results in a Cartesian relationship - every record in each table will join with every record in other table.

SELECT SalespersonID, ProductID FROM SalesPersons, Products;

Do the crosstab query.

Join the crosstab query to the Cartesian query, compound link on the SalespersonID and ProductID fields, join type "Include all records from {Cartesian query name}..."

[kestefon]

Awesome, thanks! One question...I tried joining the crosstab to the cartesian query with a left join, and it's resulting in duplicate records from the dataset. Any idea how I can get rid of those?

[June7]

Did you do compound join?

[kestefon]

What would that look like? I wrote it into the WHERE clause, i.e. "WHERE a.Field1&a.Field2 = b.Field1&b.Field2"

[June7]

I suppose that should work. But a compound join will show multiple linking lines between the tables in the query builder.

If you want to provide db for analysis, follow instructions at bottom of my post.