Date Criteria

[illusion]

Please help!!! Very important, find records that doesn't exist

hey,
I am a newbie using ms access and i need help as soon as possible.
I have already check the forums but I haven’t found anything

I have a private institute system where the students select their courses and then every month they pay their fees.I am using the following tables:

TblMembers(MemberId,StName,StSurname ..etc)
TblCourses(CourseId,Category,Level,Fees ..etc)
TblClass(MemberId,CourseId) *link table to define courses for each student
TblPayments(MemberId,CourseId,Datepaid)

What i want is to make a query to show me students who have not paid their fees since the new school year which begins at September... (i.e Datepaid < current month)
I really need help....
thanks.

 

[RichO]

Looks like you'll need to query TblMembers and TblPayments. To test the date paid against the current month, put in the following criteria in the DatePaid field of the query:

<DateAdd("d",-(DatePart("d",Date())-1),Date())

This should return all records where the date paid is prior to (less than) the first day of the current month.

See if that works.

 

[illusion]

hello,
Thanks for your reply.
I tryed the code u gave me
What i got is the firtst month when the student paid. ex.:[ 12/09/2003 ]
but i think i diferent than what i want

What i want is to make a query to show me students who have not paid their fees since the new school year which begins at September...

what i think is that, in somehow (which i don't know how) check for every student in my tblmembers if the last date in the Datepaid for each of their courses is in current month.If its not i want to sort them in order to form a report with students who own fees.

ALSO there could be students who have not paid any fees. I.E not any record in the TblPayments and i would also need them...

 

[RichO]

So there can be more than one date paid entry per student?

To get the most recent date, set your query to sort by the date paid field, descending, and then use the top values property. Set it to 1 and you will get the most recent date. You will also have to set the criteria to only look at the current student only, or you will get the most recent date paid overall.

To find students who have not paid at all (no entry in the TblPayments) query the TblMembers table and use the criteria for the member ID field:

Not In (SELECT MemberID FROM tblPayments)

This will return all students who do not have an entry in the payments table.

 

[illusion]

thank you for your reply,
Not In (SELECT MemberID FROM tblPayments)
worked just fine but it doesn't work if i put the tblpayments in order to have all toogether in one query. Nevermind i can create 2 queries/reports...

my main problem is

To get the most recent date, set your query to sort by the date paid field, descending, and then use the top values property. Set it to 1 and you will get the most recent date. You will also have to set the criteria to only look at the current student only, or you will get the most recent date paid overall.

i know how to set sorting but how i do the other things? I am a newbie and i don't know many things about Ms Access...

Thnx once again

 

[RichO]

Let me make sure I have this right...

You want to be able to query all records from TblMembers where either:

1. The most recent date paid is prior to the current month

or

2. There are no existing payment records for the student (TblPayments)

Is this correct?

Regarding the most recent date paid...

What if, for instance, the current day is May 1 and a student made their May payment on April 30. Such a query would still show that there was no May payment. How do you handle something like that?