How to query this

[Song]

I have student table:
studID LastName
1 Lin
2 Lee

payment table:
studID PaidDate PaidTo
1 4/1/2010 4/30/2010
1 5/1/2010 9/1/2010
2 4/1/2010 4/30/2010
2 5/1/2010 6/30/2010

And it's one to many relationship.

On July 1, I want to generate list of students with payments history
whose payment are due, in this case StudID2 but not StudID1

I do not have 'PaidTo' field in student table. If I add 'PaidTo' field
to student table, it'll be a burden for data entry people. Is there a
way to auto this?

 

[John W. Vinson]

I have student table:
studID LastName
1 Lin
2 Lee

payment table:
studID PaidDate PaidTo
1 4/1/2010 4/30/2010
1 5/1/2010 9/1/2010
2 4/1/2010 4/30/2010
2 5/1/2010 6/30/2010

And it's one to many relationship.

On July 1, I want to generate list of students with payments history
whose payment are due, in this case StudID2 but not StudID1

I do not have 'PaidTo' field in student table. If I add 'PaidTo' field
to student table, it'll be a burden for data entry people. Is there a
way to auto this?

What do you want to see? The maximum PaidTo date for each student? If you have
a PaidTo field in the Payment table then you certainly should NOT have such a
field in the Student table, and still less should you have anybody enter data
into it!

You mention a due date but there's no indication of how that due date might be
determined, based on what's in your table.

What determines that student 2 is not due, but student 1 is? Both are paid up
to a date that is (currently) still in the future. What's the logic?

 

[Song]

What do you want to see? The maximum PaidTo date for each student? If youhave
a PaidTo field in the Payment table then you certainly should NOT have such a
field in the Student table, and still less should you have anybody enter data
into it!

You mention a due date but there's no indication of how that due date might be
determined, based on what's in your table.

What determines that student 2 is not due, but student 1 is? Both are paid up
to a date that is (currently) still in the future.  What's the logic?
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Some students paid every month. Some paid every quater. When we
receive payment, we record paid date and paid upto (paidto). Student 1
paid upto 9/1/10. So when I run report in July to see who should pay
again, student 2 should be listed as he only paid upto 6/30. I have a
field in student table called 'closedate' which means student finished
payments and no further payment is needed. I want to run a report
monthly to see who should pay again for that month. I hope I made
myself clear.

 

[John W. Vinson]

Some students paid every month. Some paid every quater. When we
receive payment, we record paid date and paid upto (paidto). Student 1
paid upto 9/1/10. So when I run report in July to see who should pay
again, student 2 should be listed as he only paid upto 6/30. I have a
field in student table called 'closedate' which means student finished
payments and no further payment is needed. I want to run a report
monthly to see who should pay again for that month. I hope I made
myself clear.

That does help: you did not initially *say* that you'ld be running the report
in July.

There should NOT be a PaidTo or a CloseDate field in the Student table. That
information already exists in the Payments table; storing it redundantly in
the student table would be a mistake!

You can use a very simple Totals query. Join the Student table and the
Payments table on studID; select the desired student information and the
PaidTo field from payments. Change the query to a Totals query by clicking the
Greek Sigma icon on the toolbar - looks like a sideways M. Leave the default
Group By on the fields from the student table and choose "Max" as the totals
operator on PaidTo. This query will show the maximum paid-to date.

To find students whose payment is overdue, put a criterion of

<= Date()

on this MaxOfPaidTo field. If you want to find students who are getting close,
you can use a criterion

<= DateAdd("m", 1, Date())

to retrieve those students (like 2) who are coming due within a month from
today.

 

[John Spencer]

John,
I respectfully disagree with one small comment you made. There should
probably be a CloseDate field in the Students table if that means the Student
is no longer active (disenrolled, terminated, etc) and no longer will be
making payments after the CloseDate.

That does help: you did not initially *say* that you'ld be running the report
in July.

There should NOT be a PaidTo or a CloseDate field in the Student table. That
information already exists in the Payments table; storing it redundantly in
the student table would be a mistake!

You can use a very simple Totals query. Join the Student table and the
Payments table on studID; select the desired student information and the
PaidTo field from payments. Change the query to a Totals query by clicking the
Greek Sigma icon on the toolbar - looks like a sideways M. Leave the default
Group By on the fields from the student table and choose "Max" as the totals
operator on PaidTo. This query will show the maximum paid-to date.

To find students whose payment is overdue, put a criterion of

<= Date()

on this MaxOfPaidTo field. If you want to find students who are getting close,
you can use a criterion

<= DateAdd("m", 1, Date())

to retrieve those students (like 2) who are coming due within a month from
today.

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I respectfully disagree with one small comment you made. There should
probably be a CloseDate field in the Students table if that means the Student
is no longer active (disenrolled, terminated, etc) and no longer will be
making payments after the CloseDate.

ah. Yes, sorry - I misinterpreted the meaning of CloseDate; thanks for the
correction!

 

[Song]

ah. Yes, sorry - I misinterpreted the meaning of CloseDate; thanks for the
correction!

Hi, John:

I'm sure your code is working. I'll try this evening as I'm not with
my source code now. My question is: will that show payment history?
That is, if studID2 is shown, will result shows studID2's 1st payment
(april) as well?

 

[John W. Vinson]

Hi, John:

I'm sure your code is working. I'll try this evening as I'm not with
my source code now. My question is: will that show payment history?
That is, if studID2 is shown, will result shows studID2's 1st payment
(april) as well?

You'll need a separate query to do so. As I posted it, it answers the question
that you asked - how to identify overdue students.

The history data is certainly still there, the purpose of this query is to
extract the single most recent payment for each student.

 

[Song]

You'll need a separate query to do so. As I posted it, it answers the question
that you asked - how to identify overdue students.

The history data is certainly still there, the purpose of this query is to
extract the single most recent payment for each student.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Can you help me with 'separate' query to pull out history data as
well? Thanks a lot.

 

[John W. Vinson]

Can you help me with 'separate' query to pull out history data as
well? Thanks a lot.

How do you want to use this query - or these queries? What's the context? What
do you want to see on the screen, or on the report?

The history data is just there, in the payments table. If you just create a
query joining students to payments by studID, you'll see all the data. This
query can be used as the recordsource for a form, or for a report. Or, you can
use a Form based on students, with a Subform based on payments, and see all
the data.

 

[Song]

How do you want to use this query - or these queries? What's the context?What
do you want to see on the screen, or on the report?

The history data is just there, in the payments table. If you just createa
query joining students to payments by studID, you'll see all the data. This
query can be used as the recordsource for a form, or for a report. Or, you can
use a Form based on students, with a Subform based on payments, and see all
the data.

With your direction, I figured out "separate query' for the report.
All come out as expected. Thank you very much.