Query to get the number of units sold of every product at each month of the year

[abadaysheker2]

Hi

i have 2 tables: ordersTbl with the fields (orderId,Custom erId,Date)and ordersDetailsTb l with the fields(orderId, CustomerId,Prod uctId,quantity, DestailId-autonomber)

i want to get a query that tells me how many units of each products were sold every month, at any given year.

i had tried to use the count on the quantity field grouped by ProductId,and that works, but when i add the date (using a 2 calculated field with the function month() to get the month and function year() to get the year) it doesn't group by the months and Year

Does the grouping option can group by more than 1 field?

I'm new into access ,so a detailed explanation will be appreciated

Thank you very much in advance

[twinnyfo]

First, I would highly recommend you do NOT use "Date" as a field name in your tables! Yeah, yeah, yeah, I say it every time I see it and and everyone always responds with, "But it works fine the way it is!" This may be true, but you set yourself up for ambibuity within your DB. Please change it to OrderDate or something that is not a reserved word in Access! This is just a good practice to avoid such field names......

However, right now, it is impossible to provide more guidance, as we don't know how your tables are related. We could make assumptions, but that would not be good. Is OrderID the primary key for OrdersTbl or OrdersDetailsTb l? Either way, why is CustomerID in both tables, when it is only required for one Table? What is "Desta ilId-autonomber"?

What you are trying to do looks like it can be solved very easily using an aggregate query and the Year() and Month() functions, which I think you are already onto. However, without knowing your tables, we cannot build a query.

Also, it would be helpful to show us the query you have built so far, as that would have provided much of the information I have requested in the first place. We will be happy to try to troubleshoot with more information.

[abadaysheker2]

Hi twinnyfo

fist let me thank you for your interest to help, i really appreciate it.

please excuse my ignorance ,i'm new into forums, so i don't really know how to upload an image into the post, or how to get the url ,for what i understood on the internet i have to get the image first into a server.

i hope that a dropbox link will be fine,so i'm sending you an image to show you the relationships and the query

https://www.dropbox.com/s/gze0qv05ymfsrr3/relationships.PNG

https://www.dropbox.com/s/9d38k9dpzmosox0/query.PNG

please let me know if you had any trouble with the image, or if you need any more details

thank you very much

[twinnyfo]

I cannot access dropbox at work. When you post, you can click on "Advanced" and that will give you more options to add a photo.

Also, the SQL string you created would be helpful as a start.

[abadaysheker2]

THIS IS THE SQL

SELECT products_tbl.NameProduct, Month([DateEntered]) AS MonthOrder, Year([DateEntered]) AS YearOrder, Count(OrdersDetails.Quantity) AS CountOfQuantity
FROM products_tbl INNER JOIN (orders INNER JOIN OrdersDetails ON orders.[or-id] = OrdersDetails.[or-id]) ON products_tbl.NumberProduct = OrdersDetails.NumberProduct
GROUP BY products_tbl.NameProduct, Month([DateEntered]), Year([DateEntered]);

the relationships are as follow

orders-OrdersDetails ( or-id) one to many
customers-orders (cust-id) one to many
products_tbl-OrderDetails(Nu mberProduct) one to many

i'm sorry but i tried the "advanced" but the only new option that i saw is the "insert email to link", if you tell me how i will happily upload the pictures

thank you for your fast responses

[twinnyfo]

Thanks! I'll try to take a look at this soon.

P.S., don't forget to use the code tags when posting Code (clicking the "[/CODE]" button and ensuring your code is within those confines. It helps us see your code a little better.

[twinnyfo]

Now that I see your SQL string, you won't believe how close you were!

All I did was rearrange hte order of the columns and, based on my test tables which were set up according to your descriptions, I got the list, sorted by year, by month, then listing each product and how many of the products (I changed the Count to Sum).

Here it is:

SELECT Year([DateEntered]) AS YearOrder, 
    Month([DateEntered]) AS MonthOrder, 
    products_tbl.NameProduct, 
    Sum(OrdersDetails.Quantity) AS TotalSold
FROM products_tbl 
INNER JOIN (orders 
    INNER JOIN OrdersDetails 
        ON orders.[or-id] = OrdersDetails.[or-id])
        ON products_tbl.NumberProduct = OrdersDetails.NumberProduct
GROUP BY Year([DateEntered]), 
    Month([DateEntered]), 
    products_tbl.NameProduct;

P.S., in Advanced, click the button for manage attachments and you can upload pictures.

Hope this helps!

[abadaysheker2]

wow thank you very much,at first look it looks perfect , i will check it with more detail, just to make sure.

can you explain a little more about what was the error, and how did you fix it,so the next time i will be able to fix it myself

[twinnyfo]

You were grouping by the Product First, then by month, then by year (going from smaller to greater). I switched the order to go from greater (Year) to smaller (down to the product). Then, to total the number of products, I summed the quantity. If you wanted to know how many times a product was ordered, you would use count. You could also add a field for the count, which would then provide both values for your report.