Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Payment due question

Posted on 1999-06-22
12
Medium Priority
?
268 Views
Last Modified: 2010-03-19
Greeting of peace ...
I'm developing a student info. system.  I am using VB 6 as a front end and MS-ACCess 97 for backend. i need the SQL statement. Hopefully, you can help me guys.

STUDENT table will have StudentID, IntakeDay, Name and etc.
e.g. ( 9901, '01-Feb-1999', 'Rama')
     ( 9902, '01-Feb-1999', 'Ali')
       9903, '01-Jun-1999', 'John'

PAYMENT table will have PayID, StudentID, MonthPaid, AmountPaid.
(1, 9901, '02-Feb-1999', 400.00
2, 9901, '02-Mar-1999', 400.00
3, 9902, '02-Feb-1999', 400.00 )

Now, i need to print a report for those who didn't pay for a particular month.
e.g. For the month of March 1999 from 1-March-199, the SQL should display ONLY (9902, 'Ali') and NOT (9903, 'John')

For the month June 1999, it should display all 3 records which are 9901,9902 and 9903 because nobody paid so far.

So, i need the SQL Statement for that URGENTLY. I'm using MS-Access 97. If you cannot solve the problem using one sql, may be you can guide me to solve the problem asap.

Thank you.
BK Pandav
0
Comment
Question by:pandav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 

Accepted Solution

by:
lajpat earned 300 total points
ID: 1095850
Hi pandav,
  try this
SELECT student.Studentid, student.Name
FROM student
WHERE student.Studentid not in (select studentid from payment where Format(payment.MonthPaid,"mm") = ForWhichMonth)
And format(IntakeDay,"mm") <= ForWhichMonth
ForWhichMonth is parameter for month(like 03 for March)
Let me know, does it work!


0
 
LVL 5

Expert Comment

by:mayhew
ID: 1095851
Hey guys,

lajpat, not to pick apart your query (because it will probably work) but...

That query is not very efficient.  If your tables get very big (especially the payment table), that query is going to labor hard.

You'll be better off with something like:

SELECT student.Studentid, student.Name
FROM student
MINUS
SELECT distinct student.Studentid, student.Name
FROM student, payment
WHERE student.Studentid = payment.Studentid
and DatePart("m",payment.MonthPaid) = 6

You can also add in the part about the intake day if you like by tacking on another AND at the end.  Although I can't tell from the original question if that's needed or not.

Hope that's helpful.
0
 

Author Comment

by:pandav
ID: 1095852
Thank you very much lajpat and mayhew.
Lajpat answer is working fine. But when i tried mayhew answer in MS-Access 97, it says "Syntax error in FROM clause". Below is the sql syntax ...
SELECT student.Studentid
FROM student
MINUS
SELECT distinct student.Studentid
FROM student, Payment
WHERE student.Studentid = payment.Studentid
and DatePart("m",payment.MonthPaid) =3 ;

I hope mayhew will send the sql again, since the payment table will a lot of records. and i want the searching to be fast.

Thank you very much.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:lajpat
ID: 1095853
well mayhew is write. This query is not too efficient.
Let me try some another query which will run in MS Access.

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1095854
Gentlemen,

My humble apologies.  Apparently Access doesn't support MINUS.

The next best alternative is a not exists, syntax as follows:

SELECT student.Studentid, student.Name
FROM student
where not exists
(SELECT *
FROM payment
WHERE payment.Studentid = student.Studentid
and DatePart("m",payment.MonthPaid) = 3 )


Not Exists isn't always better than a MINUS but it's usually more efficient that a NOT IN.  NOT IN is usually the last resort.

Another alternative (that won't work here) is to use an outer join where one of the fields in the right table is null.  This won't work in this case because you also need to check in the right table for the proper date.  Just wanted to let you know about this in case it would help you somewhere else.

Let me know if you find this helpful.
0
 

Expert Comment

by:lajpat
ID: 1095855
Not exists will also work instead of Not in. I had considered it also.
But I really don't know which is more efficient.
Mayhew, can you tell why exactly "not exists" is more efficient than "not in"   ?

0
 

Author Comment

by:pandav
ID: 1095856
Thank you again - mayhew and lajpat. But unfortunately, the SQL below not working properly.
     SELECT student.Studentid, student.Name
     FROM student
     where not exists
     (SELECT *
     FROM payment
     WHERE payment.Studentid = student.Studentid
     and DatePart("m",payment.MonthPaid) = 3 ) ;

Assume, i have following records in the STUDENT table.
StudentID      Name      Intake
---------       ----    ------
9901              Rama      01-Feb-1999
9902              Ali      01-Feb-1999
9903              John      01-Jun-1999
(note : John is from Jun-1999 intake.)

then the payment as follows :
PayID      StudentID      MonthPaid      AmountPaid
1      9901             02-Feb-199      $400.00
2      9902           02-Feb-1999      $400.00
3      9903             02-Jun-1999      $200.00
4      9901             02-Mar-1999      $1,000.00

When i execute above sql statement, the answer is as follows  : -  

Studentid      Name
---------       ---
9902              Ali
9903              John

Note  : John is John intake, he NO NEED to pay for March 1999. Since you only search for Month, what will happen if the student is from 1998 or 2000. so, i need to check for the year also right ????

I hope you will help as soon as possible. for time being,



0
 
LVL 5

Expert Comment

by:mayhew
ID: 1095857
Right.  You need to add the check for intake month as lajpat suggested in his answer.

lajpat,

I don't fully understand the mechanics of why NOT EXISTS is more efficient than NOT IN.

It has to do with the how the query plan is executed and therefore how many rows are going to end up being scanned to give you a resultset.

Obviously, the more rows scanned, the longer the query will take.

Something about the way NOT EXISTS performs its scan allows it to look at less rows than NOT IN.
0
 

Author Comment

by:pandav
ID: 1095858
I'm STILL waiting for efficient answer ...
 
thank you again - mayhew and lajpat. But unfortunately, the SQL below NOT WORKING properly.
     SELECT student.Studentid, student.Name
     FROM student
     where not exists
     (SELECT *
     FROM payment
     WHERE payment.Studentid = student.Studentid
     and DatePart("m",payment.MonthPaid) = 3 ) ;

Assume, i have following records in the STUDENT table.
StudentID Name Intake
---------       ----    ------
9901         Rama 01-Feb-1999
9902         Ali 01-Feb-1999
9903         John 01-Jun-1999
(note : John is from Jun-1999 intake.)

then the payment as follows :
PayID StudentID MonthPaid AmountPaid
1 9901        02-Feb-199 $400.00
2 9902           02-Feb-1999 $400.00
3 9903        02-Jun-1999 $200.00
4 9901        02-Mar-1999 $1,000.00

When i execute above sql statement, the answer is as follows  : -  

Studentid Name
---------       ---
9902         Ali
9903         John

Note  : John is June 1999 intake, he NO NEED to pay for March 1999. Since you only search for Month, what will happen if the student is from 1998 or 2000. so, i need to check for the year also right ????

I hope you will help as soon as possible.

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1095859
Right.

Please reference my last comment.

You need to add the part about checking for intake month as suggested by lajpat.
0
 

Expert Comment

by:lajpat
ID: 1095860
Sorry mayhew,
 little bit late.
  About exists & In, you may be right. But still I don't understand how can be there a way to scan less number of records by "not exixts"  as the subquery is going to return the same number of records. I think it will have to compare the same number of records as with  "not in" as well as with "not exists".
 Frankly, I think they are same in performance, and it is just a programmers choice which one to use.
 
 

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1095861
I'm sorry I don't have a better low level explanation.  I'm speaking based on years of experience and what I've read in two tuning books.

I know that what you say is true if you are on Oracle and you can specifiy a cost based optimizer.

But in Access, you don't have the optimizer options that you do with Oracle.  And I believe everything is rule based (which for some reason completes a full table scan for every row in the sub query rather than just the rows asked for in the subquery).

If you're really curious, find a dog of a database and try it.  See what you come up with.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

704 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