Fill up not available data with 0

[Bobo73]

Hi to all,
I am happy to be part of the Access comunity now =) But I am at a point where intensive google approaches coul not help me out any more. I am sure, that this task is not that hard, but it is juts hard to google for this specific problem. Thats why I am asking you for help now.



In my database, there is a table wich lists all orders which looks like this:

Orders

Date	warehouse	ProductID	QTY
01.01.2016	1	1	20
03.01.2016	1	1	30
04.01.2016	1	2	22
...	...	...	...

There are several warehouses and products. all products can be ordered in all warehouses. I want to know the damand data for every day in every warehouse for every product.
Therefore I need to create a querie which create new tuples for each day whithout demand in any warehouse and fills up the demand for that day with "0".
The output schould look like this:

Demand

Date	warehouse	ProductID	QTY
01.01.2016	1	1	20
02.01.2016	1	1	0
03.01.2016	1	1	30
04.01.2016	1	1	0
...	...
01.01.2016	1	2	0
02.01.2016	1	2	0
03.01.2016	1	2	0
04.01.2016	1	2	22

Do you have any idea to realise a querie like this?

Thank You!

Bobo

[CJ_London]

from the data supplied

google 'access reserved words'
google 'sql left joins'
google 'sql Cartesian joins'

from what you are asking, you will need 4 tables, the one provided, plus one that contains all products, another that contains all warehouses and the last one to contain all dates

[Bobo73]

Thanks a lot!
I had to try different combinations of the joins, and when to use the cartesian join and when the left join, but finally i found a good solution! Thank you!

[orange]

Bobo73,

Would you like to share your solution? Someone else may have the same/similar issue in future, and having a documented solution in the forum may be useful.