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

Kamis, 26 Januari 2012

RE: [MS_AccessPros] Group by sum problem

Robin-

It wasn't deliberate! Glad to hear you got it to work.

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 10:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
>
>

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

Yahoo! Groups Links