Grouping issue

[Wick3d]

Greetings,

My apologies, for I am still a novice in all of this. I have a small issue happening when I'm creating a query though MS Query.

SELECT Accounts.PurchaseDate, Sum(MasterPayments.PaymentAmount) AS 'Sum of PaymentAmount', Ownership.OwnerName
FROM E.dbo.Accounts Accounts, E.dbo.MasterPayments MasterPayments, E.dbo.Ownership Ownership
WHERE Accounts.OwnershipID = Ownership.OwnershipID AND Accounts.AccountID = MasterPayments.Account
GROUP BY Accounts.PurchaseDate, Ownership.OwnerName, MasterPayments.PaymentDate
HAVING (Accounts.PurchaseDate>{ts '2009-01-01 00:00:00'}) AND (MasterPayments.PaymentDate<=getdate())
ORDER BY Accounts.PurchaseDate

Is it possible to have the PaymentDate as criteria, but NOT have it grouped by? When it uses PaymentDate, it splits up the PaymentAmounts into each paymentdate, but the paymentdate isn't even being shown on the query. Im trying to sum the PaymentAmount and only group it by PurchaseDate.

Wes

 

[Wick3d]

Figured it out myself. You have to take the PaymentDate out of GROUP BY, and put PaymentDate <getdate() into WHERE instead of HAVING. Was hoping MS Query was able to do it for me like in access, but apparently not. Learn something new everyday.

Wes