Insert a month that does not exist in the orig.table

[fredong]

I have a table that contains columns SalesRep, customer, vendor, tranmonth, totalsale. I need to insert a month that does not exist under the SalesRep,customer,vendor. which I will place a totalsale value of 0. Tomake it clear Basically, I want to insert the month with the totalsale value 0 that the Salesperson have not sold to this customer under this vendor.

Thanks

 

[BillDHS]

If you create a table that has the twelve months Jan through Dec, you could do an outer join to the table and zero fill the NULLS

 

[fredong]

Can you illustrates a little bit more by showing an example thanks.

 

[BillDHS]

Table1 StaffID, Month, TotSales
Table2 Month

Select Table1.StaffID, Table2.Month, Table1.Totsales
From Table1
Left Outer Join
Table2
ON Table1.Month = Table2.Month;

 

[BillDHS]

I'm not sure I was quite right with that join, you could do the following.

Table1 StaffID, Month, TotSales
Table2 Month

Select AllComb.StaffID,
AllComb.Month,
Coalese(Table1.Totsales, 0)
From
(SELECT Table1.StaffID, Table2.Month) AllComb(ID,Month)
Left Outer Join
Table1
ON Table1.StaffID = AllComb.ID
AND Table1.Month = AllComb.Month

 

[fredong]

what is the "ALlComb" is that table2 or a function?


and what is the "ID" come from in the AllComb(ID,Month)?
Please advise.