I am used to using MySQL for most of my database work, but for a college project I have to use access for an Order System.
I am having a problem with the GROUP BY clause. In MySQL you are able to group by one field only which is really what I need to do now but I cant figure it out.
I am creating a query for the current months sales statistics. I want to group by the Order Id and calculate sums for the products (net, vat and total).
I can only get the query to execute if I put all the fields (design view) to GROUP BY (except the summation ones).
This is the SQL the query is producing:
When I run this query, because everything is being grouped, it works fine until I have different quantities of different products being ordered.
SELECT Orders.[Order Id], Orders.Date, Customers.Company, [Order Line].Quantity, Shipping.Price, Sum(Products.Price*[Order Line].Quantity)+Shipping.Price AS Net, Sum((Products.Price*[Order Line].Quantity)*0.175)+(Shipping.Price*0.175) AS VAT, [Net]+[VAT] AS Total
INNER JOIN (Products
INNER JOIN ((Customers
INNER JOIN Orders ON Customers.[Customer Id] = Orders.[Customer Id])
INNER JOIN [Order Line] ON Orders.[Order Id] = [Order Line].[Order Id]) ON Products.[Product Id] = [Order Line].[Product Id]) ON Shipping.[Shipping Id] = Orders.[Shipping Id]
GROUP BY Orders.[Order Id], Orders.Date, Customers.Company, [Order Line].Quantity, Shipping.Price
HAVING (((Orders.Date)>="01/" & Format(Date(),"mm/yyyy") And (Orders.Date)<Format(DateAdd("m",1,Date()),"mm/yyyy")))
ORDER BY Orders.Date;
So really my question is, is there a way to group only by the Order Id so that all the records are combined into one order with the value summed up?
If you need any more information about the query or my database structure please ask.
Thanks in advance for your help!