I have a database that involves moving product from one status to the next. When the status is changed, it saves the date of the change, and from where the product was moved.
Ex. of one product moving from one stage to the next.
11/1/06 "Status 1"
11/1/06 "Status 1"
11/2/06 "Status 1"
11/5/06 "Status 1"
11/5/06 "Status 1"
I use a query to count the number of the occurences for a particular status on a particular day. Therefore, the above information becomes funneled into:
11/1/06 2
11/2/06 1
11/5/06 2
When this information is charted, I have the number of the occurences along the Y axis with the dates along the X. The problem is twofold:
1) When I create a graph from 11/1/06 to 11/5/06, the graph will display the dates for 11/3 and 11/4, but it will natrually be 0. However, the 0 is NOT included in the trendline that is automatically created. I need the trendline to take this into account. So in effect, I need to know that a certain date on which there were supposed to be things moved, there wasn't.
2) In other cases where I do NOT want the graph to display certain dates, such as weekends or holidays, those dates are included as well. I need a way to have the chart not display those dates when I do not want them to.
I have tried changing the chart X axis where the dates are shown to text values, but this just changes them to numbers. I've also tried changing the table in which the number of occurences is stored to have the date value be a text value, but the chart still determines that it is a date and adjusts the added dates accordingly.
Is there any way to take care of these issues?
Thank you!