GROUP BY Access

[-Ice-php]

Hey,

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;

When I run this query, because everything is being grouped, it works fine until I have different quantities of different products being ordered.

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-php]

Hey,

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!

[r937]

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).

you were actually experiencing Access enforcing the standard

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!!!