Calculation Mind Bender Qry and Report 1

[jeffshex]

I'm attempting to put together a query and report to do some calculations. It's pulling from 3 tables and I need to apply some criteria depending on what a certain value is.
I have 3 tables: tblObligations, tblPayments, and tblReclaimed.
tblObligations has a ProgramYear field (ex. 2005) and Obligation ($ Currency). The other 2 tables have a Date field and Amount.

I'm attempting to get the Sum of Obligations by Year then subtract out the amounts from each of the Payments and Reclaimed table. The downside is that the year is done from July to June; so Program Year 2007 runs from July 1st 2007 to June 30th 2008.

Is there any good way to do this?

I would like to do it so the report or whatever shows:

ProgramYear  ObligationSum  PaymentsSum  ReclaimSum  Remng
==========================================================
2005          $30,000       $20,000      $1,000     $9,000
2006          $8,000        $2,000       $0.00      $6,000
etc...

If you need any more clarification let me know. I'll be happy to get more indepth.
Thanks,

 

[PHV]

Something like this (SQL code, typed, untested):

SELECT O.ProgramYear, O.ObligationSum, Nz(P.Total,0) AS PaymentsSum, Nz(R.Total,0) AS ReclaimSum, O.ObligationSum-Nz(P.Total,0)-Nz(R.Total,0) AS Remng
FROM ((SELECT ProgramYear, Sum(Obligation) AS ObligationSum
FROM tblObligations GROUP BY ProgramYear
) AS O
LEFT JOIN (SELECT Year([date field]-181) AS ProgramYear, Sum([Amount field]) As Total
FROM tblPayments GROUP BY Year([date field]-181)
) AS P ON O.ProgramYear = P.ProgramYear)
LEFT JOIN (SELECT Year([date field]-181) AS ProgramYear, Sum([Amount field]) As Total
FROM tblReclaimed GROUP BY Year([date field]-181)
) AS R ON O.ProgramYear = R.ProgramYear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jeffshex]

I have 2 questions here.
1. My program year is a text type field - would that cause a problem with this?

2. What is the -181 doing? Converting that year to begin July 1 or something?

I think I see what's going on, I'll have a little more time to test things tomorrow.
Thanks again PHV!

 

[jeffshex]

Ok, so I broke the queries down and tested them one by one and they work. When I do them as one whole query, None of the sums show for the tblPayments and tblReclaimed. (They just show blank.)
This is what I'm running:

SELECT O.ProgramYear, O.ObligationSum, Nz(P.Total,0) AS PaymentsSum, Nz(R.Total,0) AS ReclaimSum, O.ObligationSum-Nz(P.Total,0)-Nz(R.Total,0) AS Remng
FROM ([SELECT ProgramYear, Sum(Obligation) AS ObligationSum
FROM tblObligations GROUP BY ProgramYear
]. AS O LEFT JOIN [SELECT Year([PaymentDate]-181) AS ProgramYear, Sum([PaymentDate]) As Total
FROM tblPayments GROUP BY Year([PaymentDate]-181)
]. AS P ON O.ProgramYear = P.ProgramYear) LEFT JOIN [SELECT Year([ReclaimDate]-181) AS ProgramYear, Sum([ReclaimAmount]) As Total
FROM tblReclaimed GROUP BY Year([ReclaimDate]-181)
]. AS R ON O.ProgramYear = R.ProgramYear;

Could this be caused by the dates?
Since tblObligations has the field ProgramYear and is set to a date field with a format of yyyy. The other date fields for tblPayments and tblReclaimed are set as mm/dd/yyyy format.
Would that cause a problem?

 

[PHV]

What about this ?
SELECT [!]O.Year[/!], O.ObligationSum, Nz(P.Total,0) AS PaymentsSum, Nz(R.Total,0) AS ReclaimSum, O.ObligationSum-Nz(P.Total,0)-Nz(R.Total,0) AS Remng
FROM ((SELECT [!]Year(ProgramYear) AS [Year][/!], Sum(Obligation) AS ObligationSum
FROM tblObligations GROUP BY [!]Year(ProgramYear)[/!]
) AS O
LEFT JOIN (SELECT Year([PaymentDate]-181) AS ProgramYear, Sum([Payment[!]Amount[/!]]) As Total
FROM tblPayments GROUP BY Year([PaymentDate]-181)
) AS P ON [!]O.Year[/!] = P.ProgramYear)
LEFT JOIN (SELECT Year([ReclaimDate]-181) AS ProgramYear, Sum([ReclaimAmount]) As Total
FROM tblReclaimed GROUP BY Year([ReclaimDate]-181)
) AS R ON [!]O.Year[/!] = R.ProgramYear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jeffshex]

Awesome. I see that I goofed on the PaymentAmount - sorry about that. Looks like it's doing what I need it to.
Thanks again PHV!!!

 

[jeffshex]

If I wanted to construct a query almost identitical to this, just adding in an additonal grouping field named YouthID. (YouthID is the PK in tblObligations and the FK in the other tables).

Would I just be adding an O.YouthID in the top Select statement, an additional grouping level, and also an AND statement with the YouthID with the LEFT JOIN statements?

Thoughts?