Stacked bar chart

[Bob]

I am trying to generate a stacked bar chart that will show the number of
sales and deliveries each month over a year's time.
My tables include the following fields

tblInvoice
(PK) invoice#
SaleDate
DeliveryDate
...

tblOrderDetail
(PK) id
invoice#
Style
...

I Think I want to put together a query that will count the number of
invoices for each style sold each month, and the number of invoices
delivered each month but each time I try to build the query it comes up with
a count of 1 for each invoice.

Would it be better to export this data into Excel and work it from there?

Thanks

 

[Guest]

Hi Bob,

but each time I try to build the query it comes up with
a count of 1 for each invoice.

It would be helpful if you provided the SQL statement for your query. To
view your SQL statement, click on View > SQL View in query design. Copy the
SQL statement (select it & Ctrl C) and then paste into a reply (Ctrl V). Here
is a SQL statement that might work for you:

SELECT Month([SaleDate]) AS [Month Sold],
Count(Month([SaleDate])) AS [Number of Orders], tblOrderDetail.Style
FROM tblInvoice
INNER JOIN tblOrderDetail ON tblInvoice.[invoice#] = tblOrderDetail.[invoice#]
GROUP BY Month([SaleDate]), tblOrderDetail.Style
ORDER BY Month([SaleDate]), Count(Month([SaleDate]));



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________