Solved

Challenge Question

Posted on 2002-03-17
4
163 Views
Last Modified: 2010-05-02
Hi Friends,
  I have three tables.

FeeDetails:

classcode
term1amt
term2amt
term3amt
term1duedate
term2duedate
term3duedate

Students:

studentno
name
concession

Receipts:

receiptno
studno
termno
amount
rtdate


I want a query which shows all the students who have not payed their amount till the term1duedate based on ther termno.

Some students may have concession. The Query should calculate amount for a student who have the concession and that should check with total amount payed by that student(receipts) till date in that term(1/2/3) is less than the actual amount to be payed in that term(from feedetails).

Pls give me the query.
Any suggession is greatly appreciated.
Thanking you.
0
Comment
Question by:mrajanikrishna
  • 2
  • 2
4 Comments
 

Expert Comment

by:swh062500
ID: 6874661
    I don't see a clear connection between the Receipts table and the FeeDetails table.  FeeDetails.ClassCode doesn't seem to connect to either of the 2 tables.
     Also, howmany receipts per TermNo will one StudNo have?  1 or many?  I am wondering if per student, the receipts need to be summed, or if one record is enough.  
0
 

Accepted Solution

by:
swh062500 earned 50 total points
ID: 6874687
    I think this is close to what you want.  I'm not sure about how FeeDetails.ClassCode is being used.  

     The code below is using a LEFT in case there are no receipts for a particular student.  You will need to

select s.studentNo, s.Name
from (students as S left join receipts as R
on s.studentNo = r.studno) , FeeDetails as FD

HAVING sum(amount) + concession <=  FD.Term1Amt
WHERE r.termno = 1
AND r.rtdate <  FD.Term1DueDate
AND FD.ClassCode = ???
GROUP BY s.studentNo, R.termo
0
 

Author Comment

by:mrajanikrishna
ID: 6876591
Yes, I need the same query

Thanks
0
 

Author Comment

by:mrajanikrishna
ID: 6896047
Hi SWH,

    I am not getting the O/P. Error is   Join not supported.

What is the problem.  


Give  me reply.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question