I have data in Access Database which contains data for multiple days. But it sometime have missing data for some dates.
In example, I have data for
myDate Location Price
11/1/2013 South 10
11/1/2013 West 20
11/1/2013 East 10
11/2/2013 South 10
11/2/2013 West 20
11/2/2013 East 10
11/4/2013 South 10 <---- 11/3/2013 Data Missing
11/4/2013 West 30
11/4/2013 East 10
The way I tried to solve it was to find missing date in Access Database, and filled it with Null value using calender table.
myDate Location Price
11/1/2013 South 10
11/1/2013 West 20
11/1/2013 East 10
11/2/2013 South 10
11/2/2013 West 20
11/2/2013 East 10
11/3/2013 <---- Null values
11/4/2013 South 10
11/4/2013 West 30
11/4/2013 East 10
But when I use crosstab query to get get price for each location, 11/3/2013 is skipped because it doesn't have any location related to it.
myDate South West East
11/1/2013 10 20 10
11/2/2013 10 20 10 <---- 11/3/2013 Data skipped
11/4/2013 10 30 10
What I want my solution to be is following:
myDate South West East
11/1/2013 10 20 10
11/2/2013 10 20 10
11/3/2013
11/4/2013 10 30 10
I thought I would be able to use NZ function when I'm calling this data from Access, and whenever I find null value, I can just put #N/A error in Excel spreadsheet so I would be able to catch what data is missing and fill it in later.
How would I accomplish this? Am I doing the right way or is there better way?
Any help or hint would be greatly appreciated.
See Question&Answers more detail:os