MS Access 2000.
I have this Query:
SELECT [StudentDataTest].[FOLIO], [Receipts].[ Code], SUM([Receipts.Amount]) AS Total
FROM StudentDataTest INNER JOIN Receipts ON [StudentDataTest].[FOLIO]=[Receipts].[Folio]
GROUP BY [StudentDataTest].[FOLIO], [Receipts].
I have this Query:
SELECT [StudentDataTest].[FOLIO], [Receipts].[ Code], SUM([Receipts.Amount]) AS Total
FROM StudentDataTest INNER JOIN Receipts ON [StudentDataTest].[FOLIO]=[Receipts].[Folio]
GROUP BY [StudentDataTest].[FOLIO], [Receipts].
Code:
;[/b]
[b]Folio:[/b] is a Student's Unique ID.
[b]Code:[/b] is a Type of Payment [eg: TUI01 (January Tuition)]
[b]SUM([Receipts.Amount]) AS Total: [/b] gives me the Total of all payments by this Folio for this Code - [eg: a student may make several part-payments for TUI01].
What I want to do is - for example:
If the First 3 characters of Code are 'TUI'then
If the SUM([Receipts.Amount]) < 320 then
Display this record.
IE: For each of the various '[Receipts].[ Code]' types I want to only see those payments which are less than the required amount for that Fee.
Eg: for all the Tuition Fee Payments [the Codes all start with 'TUI'] I only want to see those that SUM up to less than 320.
I have looked at 'Case' & 'If . . . Else' statement examples but have not been able to figure out how to use them for my particular situation.
I would greatly appreciate any help you can give me.
Robeen