Results 1 to 3 of 3
Thread: GROUP BY Access
Dec 27, 2006, 15:28 #1
GROUP BY Access
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:
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 FROM Shipping 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!-Ice
Dec 27, 2006, 17:13 #2
I have just realised how stupid I actually am. I don't need the Shipping or the Quantity to be shown at all. By Omitting them from being selected the query works as expected!-Ice
Dec 27, 2006, 18:48 #3
- Join Date
- Jul 2002
- Toronto, Canada
- 53 Post(s)
- 2 Thread(s)
the query may never work as expected until you write a correct GROUP BY
according to the sql standard, a correct GROUP BY contains every non-aggregate column in the SELECT clause
in other words, when you wrote this --I can only get the query to execute if I put all the fields (design view) to GROUP BY (except the summation ones).
the fact that mysql doesn't enforce the sql standard is doing an injustice to many people who never learn what the sql standard for GROUP BY is
but alas, that's not the end of the story
Access has its own flaws -- in particular, you cannot, according to the sql standard, reference a column alias in the SELECT clause, as you are doing here with [Net]+[VAT] AS Total
mysql would give you an error on that
so, what's a person to do?
stick to the standard in all cases, chances are it will work!!!