Help Restricting Rows in Result Set.

[Robeen]

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].

;[/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

 

[Robeen]

Sorry - For some reason the last line of my query showed up with an error - here it is again without that weird last character - "

 [/b]" - which I get when I try & enter the word 'Code' with square brackets [] around it. 

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];

Thanks in advance.

Robeen

 

[Trendsetter]

I have had a look at this a couple of times and was hoping somebody else would pick it up but.....
You have not said how you want to see these records so I will just give you the outline for isolating them:
Dim strSQL as String
strSQL = "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];"
Dim rst as Recordset
Set rst = CurrentDB.OpenRecordSet("strSQL&quot
rst.MoveFirst
Do While Not rst.EOF
If Left(Code,3) = &quot;TUI&quot; And Total < 320 Then
Do something here........
ElseIf Left(Code,3) = &quot;PEN&quot; And Total < 540 Then
Do something here........
Else
Something else.........
End if
rst.MoveNext
Loop
rst.close
Set rst = Nothing



Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[Robeen]

Thanks Trendsetter,

I'm sorry if I miscommunicated my request.

What I was hoping to get was a way to get the rows I want with the SQL alone.

I want to only see those Sum(Receipts.Amount)
which are less than 320
if the Receipts.Code Like(&quot;TUI%&quot

When I open my recordset with the strSQL - I only want it to contain those rows for which the full amount required has not been paid.

I was wondering if SQL 'Case' Statements could be used for the various different types of Fees . . . of which &quot;TUI%&quot; is one. . . or if there is a way to get what I want using 'If . . . Else . . .' in SQL.

I am working in VB but want to find out if it is possible to do this in SQL so that I my recordset has only short payments from the SQL before I start coding in VB.

Thanks!

Robeen

 

[CCLINT]

Try:

SELECT [StudentDataTest].[FOLIO], [Receipts].[ Code], SUM([Receipts.Amount]) AS Total
FROM StudentDataTest INNER JOIN Receipts ON [StudentDataTest].[FOLIO]=[Receipts].[Folio]
WHERE [Receipts].[ Code] LIKE 'TUI*'
GROUP BY [StudentDataTest].[FOLIO], [Receipts].[ Code]
HAVING SUM([Receipts.Amount])<320


If you are using an ADO connection object, ADO recordset object, or SQL Pass Through Query you will need to replace the asterisk mark with percentage mark

LIKE 'TUI%'

 

[Robeen]

Hi CCLINT,

This definitely works for that one payment code. Thanks! That's unqualified.

SELECT [StudentDataTest].[Folio], [Receipts].[ Code], SUM([Receipts].[Amount]) AS Total
FROM [StudentDataTest] INNER JOIN [Receipts] ON [StudentDataTest].[Folio]=[Receipts].[Folio]
WHERE [Receipts].[ Code] LIKE 'TUI%'
GROUP BY [StudentDataTest].[Folio], [Receipts].[ Code]
HAVING SUM([Receipts].[Amount])<320
ORDER BY [StudentDataTest].[Folio]

Just so you know - I've finished coding the program using my initial SQL Statement - so there's no urgency.

However, in my initial question what I had wanted was to be able to test for all the different payment codes in the same SQL Statement - and if a payment is of a certain type [eg: HOS03 - March Hostel Fees] and the amount of its 'Sum'med payments is less than the required amount for that fee - then include it in the result set.

Like I said - I've already done this in VB - but I'd still like to know if it can be done in SQL.

I appreciate that I now know how to do it with at least one of the payment codes at a time.

Thanks,

Robeen

 

[CCLINT]

Oh. Then you should seperate the code type into two fields: MainCodeID and SubCodeID, and combined them when needed (SELECT MainCodeID + SubCodeID AS

....

That way you can easily Group on the MainCodeID, or whatever you want to call it.