Query

Rey cleaned up your syntax but to do it he had to make some assumptions.  I'm not sure they were all correct.

SELECT Orders.OrderID, Orders.Create_Date, Sum(OrderDetails.LineSubTotal) AS SubTotal
FROM Orders INNER JOIN OrderDetails]ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Orders.OrderID, Orders.Create_Date
HAVING Sum(OrderDetails.LineSubTotal)>500
ORDER BY Orders.Create_Date

The assumptions I made are
Create_Date comes from Orders and LineSubTotal comes from OrderDetails

If either assumption is incorrect, you will need to change the table name qualifier.

maybe this will do it

 SELECT Orders.OrderID, Orders.Create_Date, Sum(LineSubTotal) AS SubTotal
  FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
  GROUP BY Orders.OrderID, Orders.Create_Date
  HAVING Sum(LineSubTotal)>500
  ORDER BY Orders.Create_Date

Created this from Data in Northwind:

SELECT D.OrderID, O.OrderDate, D.orderTotal
FROM Orders AS O INNER JOIN (SELECT [Order Details].OrderID, Sum([UnitPrice]*[Quantity]) AS orderTotal
FROM [Order Details]
GROUP BY [Order Details].OrderID)  AS D ON O.OrderID = D.OrderID
WHERE (((D.orderTotal)>500))
ORDER BY O.OrderDate

Select allOpen in new window

hnasr,
Access doesn't optimize sub queries so there is nothing to be gained by using them when you have an equivalent join available.  Once Rey fixed the syntax, there was nothing wrong with the original query.

I agree with Pat and find that adding a subquery usually slows things down.

the reasons I mentioned WHERE is because:
1. it is good to know the difference between Where and Having
2. I wasn't sure what you meant by 'whose amounts' ... total invoice or amount of a line item

@PatHartman,
I can't see an obvious reason, at least to me, for this comment. I was working on the issue and noticed the solution when page was refreshed.

speaking on behalf of Pat ... she, like I, is just trying to help you learn more ~

you would notice a difference if there was a lot of data

I only mentioned it because you frequently post subquery solutions.  There are situations where joins won't solve the problem and subqueries are necessary so knowing how to use one is useful but they are not the "go to" solution for Access and given their inefficiency when the BE is Jet/ACE they should not be used arbitrarily.

@PatHartman

Please don't misunderstand me, but I don't like your way commenting on experts.
If I remember, you repeated this more than once.

If some one asks how to use a method, you tend to direct him to change the design.
Some authors' comments demonstrate such behavior.

Once Rey fixed the syntax, there was nothing wrong with the original query.

I told you why that comment was posted. #a41918665
But as usual you kept elaborating.

I hope you refrain from doing that.

If someone asked me how to load his gun, I wouldn't do it either.  People come here because they need help.  I'm not going to help them to create something I know will be a problem.  Sometimes they are on the right track and it is simply a syntax issue as this one was but frequently people just guess at what solution they need because they don't have the framework to choose a path let alone the technical expertise to implement it.  So they ask "how" can I do "something" thing rather than "what" should I do simply because they don't know what their options are.  It's why help is so hard for newbies to use.  If they don't know the right search terms, they are unlikely to come up with any answer let alone the best answer.

I ran into a real world example of this a few weeks ago.  My friend and I went to Boston for the weekend to play in a bridge tournament.  When we left the game on Saturday night, she missed a turn.  Easy enough to do as any one who has ever driven in Boston can tell you.  I suggested that she turn around immediately.  She elected to continue on and let the GPS work it out.  Well, the wrong turn got us to the wrong side of the Charles and the GPS took us back to the hotel on two highways.  So a 10 minute trip took 45.  In the same situation, would you have not suggested an immediate turn around?  Not everyone listens, but you really have to try.

You just keep your blinders on and solve the immediate issue.  Never look beyond the exact question that was asked.  I'll look at the bigger picture to see if there might be a better option.

one thing to keep in mind ... geeks are, well, geeky! We tend to get to the point quickly and that may seem blunt. To others (most of the rest of the world!), it may seem rude. Please don't take it that way!

>  direct him to change the design"

Perhaps changing design may be beyond the poster's control, but is it not good to know anyway? For the next time or when it is? Teachers try to give understanding of what is missing.

> "you kept elaborating"

that is what a teacher does ... to help the poster build better database applications ;) ~ I realize that by the time someone posts, they are frustrated and not necessarily in learning mode ... but please realize that teachers teach.

I am jumping in because I have "learned" not to be so blunt ... but it is my nature to be that way so I defend others who are very knowledgeable and are that way as well.

on another note:
happy Ann got something that works for her (smile)