M
MrBen.Email
Hello!
I made this DB ages ago, and everything worked fine. Ran it yesterday
to show the main manager here at work, and all hell broke loose. My
query now does not work, even though it was tested thoroughly when it
was made. Here's the SQL:
SELECT [Order Details].OrderID, Customers.FirstName,
Customers.LastName, Customers.BillingAddress, Orders.Comment,
Customers.Notes, Orders.DeliveryDate, Customers.FaxNumber,
Customers.Address2, Customers.Address3, Orders.OrderDate,
Customers.PhoneNumber, Customers.DeliveryHours, Customers.Address4,
Customers.City, Customers.StateOrProvince, Customers.ZIPCode,
Customers.CompanyName, Products.ProductName, Products.UnitPrice, [Order
Details].Quantity, [Quantity]*[Order Details]!UnitPrice AS SubTotal,
[Order Details].Discount, [SubTotal]*(1-[Discount]) AS [SubTotal With
Discount], Orders.SalesTaxRate, Sum([SubTotal With
Discount]*(1+[SalesTaxRate])) AS [Total Price], Customers.CustomerID,
[Order Details].ProductSize, [Order Details].ProductID,
Orders.PaymentMethod, Orders.PaymentHouseNo, Orders.PaymentPostCode,
Orders.CardHolderName, Orders.CardNumber, Orders.CardStartDate,
Orders.CardExpiryDate, Orders.CardIssueNumber, Orders.CardCVVNumber
FROM Customers INNER JOIN (Orders INNER JOIN (Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID =
Orders.CustomerID
WHERE (((Orders.OrderDate)=Date()))
ORDER BY [Order Details].OrderID, Products.ProductName,
Orders.SalesTaxRate;
It's a simple select query, with some calculations.
Can anyone spot any mistakes in this?
The error I get is: "You tried to execute a query that does not include
the specified expression 'OrderID' as part of an aggregate function"
I would post the whole DB, but work will not allow me to do so.
Any help anyone could offer would be greatly appreciated.
Many thanks,
Ben
I made this DB ages ago, and everything worked fine. Ran it yesterday
to show the main manager here at work, and all hell broke loose. My
query now does not work, even though it was tested thoroughly when it
was made. Here's the SQL:
SELECT [Order Details].OrderID, Customers.FirstName,
Customers.LastName, Customers.BillingAddress, Orders.Comment,
Customers.Notes, Orders.DeliveryDate, Customers.FaxNumber,
Customers.Address2, Customers.Address3, Orders.OrderDate,
Customers.PhoneNumber, Customers.DeliveryHours, Customers.Address4,
Customers.City, Customers.StateOrProvince, Customers.ZIPCode,
Customers.CompanyName, Products.ProductName, Products.UnitPrice, [Order
Details].Quantity, [Quantity]*[Order Details]!UnitPrice AS SubTotal,
[Order Details].Discount, [SubTotal]*(1-[Discount]) AS [SubTotal With
Discount], Orders.SalesTaxRate, Sum([SubTotal With
Discount]*(1+[SalesTaxRate])) AS [Total Price], Customers.CustomerID,
[Order Details].ProductSize, [Order Details].ProductID,
Orders.PaymentMethod, Orders.PaymentHouseNo, Orders.PaymentPostCode,
Orders.CardHolderName, Orders.CardNumber, Orders.CardStartDate,
Orders.CardExpiryDate, Orders.CardIssueNumber, Orders.CardCVVNumber
FROM Customers INNER JOIN (Orders INNER JOIN (Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID =
Orders.CustomerID
WHERE (((Orders.OrderDate)=Date()))
ORDER BY [Order Details].OrderID, Products.ProductName,
Orders.SalesTaxRate;
It's a simple select query, with some calculations.
Can anyone spot any mistakes in this?
The error I get is: "You tried to execute a query that does not include
the specified expression 'OrderID' as part of an aggregate function"
I would post the whole DB, but work will not allow me to do so.
Any help anyone could offer would be greatly appreciated.
Many thanks,
Ben