Access summing items by month

[Hoplite]

Hello everybody, I have the following question:

Lets say we have a tables where we record sales of cars:

TABLE1
order_id
customer_id
order_date

TABLE2
order_id
product_id
quantity

TABLE3
product_id
product_name
product_price
product_model

Relationships between tables are as follows: TABLE1 one-to-many TABLE2, TABLE3 one-to-many TABLE2.

My question is how to make a query which can say how many particular models were sold in specific month and what was the cost of those sales. The trick is that order_date is in normal date format like dd/mm/yy.

I have tried variety of COUNT operations in my queries but everything seems to be doing something wrong and results are far from what I need.

[nico5038]

Looks to me you should first create a query with a JOIN between table1 to table2 and a JOIN between table2 to table3.
For the Datefield use in the query:
Year(order_date) & right("00" & Month(order_date),2) as YearMonth

Now you can use that query to select the model and make and create a GroupBy query to sum the number of models.

Getting the idea ?

Nic;o)

[NeoPa]

To get an output field to sort and group on, use :

Format([Order_Date],'yyyymm')

This will provide a result which is unique per month and also will sort in month order.
Let us know if you have any difficulty putting a basic query together with that.