Two sums from one table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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;
 
That was *exactly* what I was looking for. THANKS!

Brad

TPratt said:
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;



Brad said:
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
Click to expand...
Click to expand...
 
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

TPratt said:
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;



Brad said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top