Grouping by Date

I have a Table of Payments with PaymentAmount, PaymentDate and PaidToID.
PaymentDate is set to Now() On any day, a number of payments can be made to
the same person. I want to create a query that will add together all
payments paid to that person paid within say 1/2 hour of each other. I have
tried
Round(CDec(PaymentDate,1)) seems to allow about 2 1/2 hour spread and
Round(CDec(PaymentDate,2)) is too sensitive e.g.
Round(CDec(#23/10/2007 12:15:00#),2) gives 39378.51 whilst
Round(CDec(#23/10/2007 12:05:00#),2) gives 39378.5

SQL looks something like

SELECT Member.PaidToID, Sum(Payments.PaymentAmount) AS SumOfPaymentAmount,
Round([PaymentDate],2) AS Expr1
FROM PaymentMethods INNER JOIN (Member INNER JOIN Payments ON
Member.MemberID = Payments.MemberID) ON PaymentMethods.PaymentMethodID =
Payments.PaymentMethodID
GROUP BY Member.PaidToID, Round([PaymentDate],2);

Any bright ideas gratefully received

Thanks

Phil

Re: Grouping by Date

Maybe not a bright idea, and certainly not mine, but this should group
by the half hour.

SELECT Member.PaidToID, Sum(Payments.PaymentAmount) AS
SumOfPaymentAmount,
DATEADD("n",INT(DATEDIFF("n",0,Payments.PaymentDate) / 30) * 30, 0) AS
HalfHour
FROM PaymentMethods INNER JOIN (Member INNER JOIN Payments ON
Member.MemberID = Payments.MemberID) ON
PaymentMethods.PaymentMethodID=
Payments.PaymentMethodID
GROUP BY Member.PaidToID, INT(DATEDIFF("n",0,Payments.PaymentDate) /
30);



On Tue, 23 Oct 2007 09:07:26 +0100, "Phil Stanton"
wrote:

>Any bright ideas gratefully received

Re: Grouping by Date

Brilliant

Works a treat

Thanks

Phil


"Michael Gramelspacher" wrote in message
news:7dnrh31trn7ngphcmmll9q3topodhqseag@4ax.com...
> Maybe not a bright idea, and certainly not mine, but this should group
> by the half hour.
>
> SELECT Member.PaidToID, Sum(Payments.PaymentAmount) AS
> SumOfPaymentAmount,
> DATEADD("n",INT(DATEDIFF("n",0,Payments.PaymentDate) / 30) * 30, 0) AS
> HalfHour
> FROM PaymentMethods INNER JOIN (Member INNER JOIN Payments ON
> Member.MemberID = Payments.MemberID) ON
> PaymentMethods.PaymentMethodID=
> Payments.PaymentMethodID
> GROUP BY Member.PaidToID, INT(DATEDIFF("n",0,Payments.PaymentDate) /
> 30);
>
>
>
> On Tue, 23 Oct 2007 09:07:26 +0100, "Phil Stanton"
> wrote:
>
>>Any bright ideas gratefully received