'Mapping Table' query including all combinations

[rewritable]

Good day!



Here's a problem I encountered in the planning step of a database that I've stuck and don't know how to even approach.
Basically I will have a table with information about shops ("tblShops"), a table of information about types of units ("tblTypes"), and then a table of actual units ("tblUnits"), which list unique information about each unit as well as its type and shop it is in.



It is necessary to look at units at each shop and return the minimum count of units per type to warn the user that at least one of the types in that shop is running low.
What I am trying to do is a get a mapping table that list all combinations of shops and types and count of how many units are there for each combination. The I could use the Dmin function to get the minumum count. A simple select query does the job as long as there are units for all combinations, but it doesn't display the combination at all if there are no units for it, which causes problems as if there are 0 units of certain type in any shop, it won't appear in query, thus Dmin won't return what I need.

I've tried using nz function, crosstab queries and two-step queries with left/right joins, but I've failed to get anything close to what I need.

Here are the contents of my test database:

Shops:

ID	ShopName
1	PS1
2	PS2
3	PS3
4	PS4

Types:

ID	TypeName
1	K1
2	k2
3	k3
4	k4
5	k5
6	k6
7	k7

Units:

ID	ShopID	TypeID	Information
2	3	4	aaa
3	2	3	bbb
4	2	3	ccc
5	2	4	dddd
6	2	2	eee
7	4	3	fff
8	2	4	ggg
9	2	4	hhhh
11	2	2	jj
12	3	2	kkkkk
14	4	7	llk

This is the returned result of simple select query.

SELECT tblShops.ShopName, tblTypes.TypeName, Count(nz([tblUnits].[ID])) AS Expr1FROM tblTypes INNER JOIN (tblShops INNER JOIN tblUnits ON tblShops.ID = tblUnits.ShopID) ON tblTypes.ID = tblUnits.TypeID
GROUP BY tblShops.ShopName, tblTypes.TypeName;

ShopName	TypeName	Expr1
PS2	k2	2
PS2	k3	2
PS2	k4	3
PS3	k2	1
PS3	k4	1
PS4	k3	1
PS4	k7	1

The result I'm trying to get should look like this:

ShopName	TypeName	Expr1
PS1	k1	0
PS1	k2	0
PS1	k3	0
PS1	k4	0
PS1	k5	0
PS1	k6	0
PS1	k7	0
PS2	k1	0
PS2	k2	2
PS2	k3	2
PS2	k4	3
PS2	k5	0
PS2	k6	0
PS2	k7	0
PS3	k1	0
PS3	k2	2
PS3	k3	0
PS3	k4	1
PS3	k5	0
PS3	k6	0
PS3	k7	0
PS4	k1	0
PS4	k2	0
PS4	k3	1
PS4	k4	0
PS4	k5	0
PS4	k6	0
PS4	k7	1

I hope it is possible to achieve with queries. Alternative as I see it would be to create a table that list only counts and write code that update the counts at each change in stock and adds/deletes rows at each change in possible types and shops.
I hope I explained my problem thoroughly enough. Attached is my test database that contains most of my attempts so far. Hopefully you can point me to the right direction.

[ssanfu]

I think this is what you are trying to do. I first created a Cartesian product using the Shops table and the Types table. Then I joined that query with the simple query you posted in a third query.
I linked both queries by ShopID and TypeID and set left joins on both.
Check the formula in the Units query......

[rewritable]

Thank you very much, that's exactly I was trying to get!
I wasn't aware of possibility of creating Cartesian product this way, that sure will come handy a lot in future.