ACCESS FEED: RE: [MS_AccessPros] Group by sum problem

Rabu, 25 Januari 2012

RE: [MS_AccessPros] Group by sum problem

Robin-

You're applying the filter on Amount to the Sum. Try this:

SELECT Sum(PaymentsMT.Amount) As SumOfAmount, PaymentsMT.BookingID
FROM PaymentsMT
WHERE (((PaymentsMT.Amount)>1) AND ((PaymentsMT.BookingID)=135 Or
(PaymentsMT.BookingID)=301))
GROUP BY PaymentsMT.BookinID

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Lahaina, HI)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Wednesday, January 25, 2012 8:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Group by sum problem

I am using Access 2007 on a windows 7 system.

I have a query which gives me three records, one for BookingID=135
and two for BookingID=301. This is the SQL

SELECT PaymentsMT.Amount, PaymentsMT.BookingID
FROM PaymentsMT
WHERE (((PaymentsMT.Amount)>1) AND ((PaymentsMT.BookingID)=135 Or
(PaymentsMT.BookingID)=301));

I need to have a total per Booking ID and I have used this SQL:

SELECT Sum(PaymentsMT.Amount) AS SumOfAmount, PaymentsMT.BookingID
FROM PaymentsMT
GROUP BY PaymentsMT.BookingID
HAVING (((Sum(PaymentsMT.Amount))>1) AND ((PaymentsMT.BookingID)=135
Or (PaymentsMT.BookingID)=301));

which produces no records.

What have I missed?

Many thanks,

Robin Chapple

------------------------------------

Yahoo! Groups Links