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

Kamis, 26 Januari 2012

RE: [MS_AccessPros] Group by sum problem

Thanks yet again John,

That is a new technique for me, I will attempt to remember!

I also found your deliberate mistake!

Best wishes,

Robin

At 26/01/2012 05:52 PM, you wrote:
>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
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>