Date join between tables

[#1]

Hi,

Hope someone can help me with this issue. I have three tables that I wanna join together; the first one is the date table with all dates from 2011-01-01 to today, the second table is the sales table with all the sales information and the third table i the product table with information about the products.

Table 1 - Dates (contains all days from 20110101)

- DateID

- Year

- Month

- Day

- ....

Table 2 - Sales (contains a unique row for each sales)

- SalesID

- SalesDateID

- ProductID

- ....

Table 3 - Products (contains a unique row for each product)

- ProductID

- ProductName

- ....

One ProductID can have many SalesID and a ProductID may be sold one day and the next day there is no sales for that ProductID which means the ProductID will have no connection to a SalesDateID if there is no sales that day.

What I wanna do is to join SalesDateID with DateID so that I can create a chart showing the sales result for a choosen ProductID. The chart should show ALL days during a period and the number of sales - 0 if there were no sales that day.

It should look like this for at choosen ProductID:

Date	ProductID	Number of sales
2011-01-01	1234	456
2011-01-02	1234	73
2011-01-03	1234	12
2011-01-04	1234	0
2011-01-05	1234	908
2011-01-06	1234	0
2011-01-07	1234	454
2011-01-08	1234	17
2011-01-09	1234	0
.... and so on....

Hope someone can help me with the join or expression to solve this.

Thanks!

Fredrik

[er_mohit]

use after load table 2 in th end before semi colon write this where exit(SalesID);

[#3]

Hi Frederik,

please get a listbox for the date in Table 1 - Dates and the Date in Table 2 - Sales

Would be interesting whether they are the same and what dates belong to 2012 Oct.

Ciao

Klaus