Two sums from one table

[Guest]

Table is as follows:
LoanID | PayAmt | PayDate
1 | 100. | 1/1/2005
1 | 100. | 2/1/2005
1 | 100. | 3/1/2005
2 | 100. | 1/1/2005
2 | 100. | 1/16/2005
2 | 100. | 2/1/2005
2 | 100. | 2/16/2005

Is it possible to write a query that will return the sum of the payamt as
well as the sum of the payamt for payments that are dated before today? (or
just the difference between the two)

result:
LoanID | SumAll | SumPast | (Diff)
1 | 300. | 200. | 100.
2 | 400. | 300. | 100.

Thanks in advance,
Brad

 

[TPratt]

It sounds like you're looking for the DSUM function.

Try copying this SQL into a query. You may have to change the table name...

SELECT Table1.LoanID, Sum(PayAmt) AS SumAll,
DSum("[PayAmt]","Table1","[LoanID]=" & [LoanID] & " and [PayDate] < Date()")
AS SumPast, [SumAll]-[SumPast] AS Diff
FROM Table1
GROUP BY LoanID;

 

[Guest]

That was *exactly* what I was looking for. THANKS!

Brad

It sounds like you're looking for the DSUM function.

Try copying this SQL into a query. You may have to change the table name...

SELECT Table1.LoanID, Sum(PayAmt) AS SumAll,
DSum("[PayAmt]","Table1","[LoanID]=" & [LoanID] & " and [PayDate] < Date()")
AS SumPast, [SumAll]-[SumPast] AS Diff
FROM Table1
GROUP BY LoanID;

Table is as follows:
LoanID | PayAmt | PayDate
1 | 100. | 1/1/2005
1 | 100. | 2/1/2005
1 | 100. | 3/1/2005
2 | 100. | 1/1/2005
2 | 100. | 1/16/2005
2 | 100. | 2/1/2005
2 | 100. | 2/16/2005

Is it possible to write a query that will return the sum of the payamt as
well as the sum of the payamt for payments that are dated before today? (or
just the difference between the two)

result:
LoanID | SumAll | SumPast | (Diff)
1 | 300. | 200. | 100.
2 | 400. | 300. | 100.

Thanks in advance,
Brad

 

[John Spencer (MVP)]

This might be faster

SELECT Table1.LoanID,
Sum(PayAmt) AS SumAll,
SUM(IIF([PayDate]<Date(),[PayAmt],Null)) as SumPast
AS SumPast, [SumAll]-[SumPast] AS Diff
FROM Table1
GROUP BY LoanID;

That was *exactly* what I was looking for. THANKS!

Brad

It sounds like you're looking for the DSUM function.

Try copying this SQL into a query. You may have to change the table name...

SELECT Table1.LoanID, Sum(PayAmt) AS SumAll,
DSum("[PayAmt]","Table1","[LoanID]=" & [LoanID] & " and [PayDate] < Date()")
AS SumPast, [SumAll]-[SumPast] AS Diff
FROM Table1
GROUP BY LoanID;

Table is as follows:
LoanID | PayAmt | PayDate
1 | 100. | 1/1/2005
1 | 100. | 2/1/2005
1 | 100. | 3/1/2005
2 | 100. | 1/1/2005
2 | 100. | 1/16/2005
2 | 100. | 2/1/2005
2 | 100. | 2/16/2005

Is it possible to write a query that will return the sum of the payamt as
well as the sum of the payamt for payments that are dated before today? (or
just the difference between the two)

result:
LoanID | SumAll | SumPast | (Diff)
1 | 300. | 200. | 100.
2 | 400. | 300. | 100.

Thanks in advance,
Brad