Chart Dates without Blanks

[bs0d]

I have daily data set (via query) which may have dates missing. I use a line-graph to view the data. Where dates are missing, the chart will draw a line between the two points, which is visually inaccurate. If the dates are missing, I'd like to see a gap instead, or zero's. So I'm looking for the most efficient way to add missing dates, so that nothing is plotted between the two dates where days are missing.

Here are two ideas:

• VBA Loop Method: Put query to sheet -- loop through rows evaluate and insert where dates are missing (slow or inefficient?).

• Query Method: Create a time table. Do a left join to include all rows from time table, and only those that match in the data query. Limit start and end dates to match min and max of targeted data.

Any feedback would be helpful.

 

[bs0d]

I'm trying the query idea and it may not work quite like I thought. Here's what I've got:

I created a ReadingDates tables with dates from 1/1/1900 to 12/31/2100. I created a query with a left join, matching the ReadingDates - date field with the date field from my data table. When I query the data table, it is for a specific entity.

I expected to see all dates from ReadingDates, and only the data on dates that exist in both tables for the entity I selected. However, I just see the typical results with dates missing. I think this is happening because there are no entity ID's in the ReadingDates table?

Is it still possible to get what I'm after through a query approach? Because I also need to limit the dates to start and end with the min(date) and max(date) of the entity-- so I don't have a query result from year 1900 to 2100.

Thoughts?