Help using data to analyse sales
I own a furniture store and am building a program to make and track sales of inventroy items. In a nut shell I have Customers, Products, Invoices and line Items tables.
What I am trying to do is analyse my sales to track sales trends mainly via graphs. Meaning having a graph that shows the number of sales of the different colored furniture over a given period of time. Also a graph of sales by date and or time of day. And more but I figure if I can figure out how to make one it should be similar for different criteria.
My thought has been so far that I have to use the line Item table as the source for this data. What I am trying to figure is how to say, ok here is a list of all of my sales and I want a multiline line graph showing the number of sales of each of the colors over the last year. In the line item table I have a lookup field that enters say "Red" which it gets from the product table. I figure I need to do some type of count and summary to show that there were 10 "Red" 7 "Tan" and 11 "Brown" pieces sold last month. How do you recommend I set that up?
Basically the same questions here just different criteria. I want to graph sales by Date (1/1/2011), Day of Week (Thursday), and Time of day (12:30pm) and any combination of the three. Any thoughts on this one?
Thanks in advance,