Add Missing Records

[brahmabull525]

I have a table in Access with three columns (ZIPcode, Month, Sales). There are multiple ZIPcodes represented. For some ZIPcodes, in some months there were no sales. At present there is no record in the table for those cases.

I would like to add records that would show, for example:

ZIPcode: 00000
Month: January
Sales: $0

Basically, code would have to identify for each Zipcode a missing month, and then create a record for it with $0.

Hope this makes sense. If someone could help me with the code on this it would be much appreciated!

Thank you.

 

[agungsh]

You need ZIPcode, Month and your Sales tables:

ZIPcode table :
ID AutoNumber
ZIPcode Text

Month table :
ID AutoNumber
Month Text

Sales table :
ZIPcode Text
Month Text
Sales Currency

then create these queries :

MonthZIP is

SELECT ZIPcode.ZIPCode, Month.Month
FROM [Month], ZIPcode;

Summary is

SELECT MonthZIP.ZIPCode, MonthZIP.Month, IIf(IsNull([Sales]),0,[Sales]) AS Total
FROM Sales RIGHT JOIN MonthZIP ON (Sales.Month = MonthZIP.Month) AND (Sales.ZIPcode = MonthZIP.ZIPCode);

Hope this help.