Why is access filling in fields in query results

[Robbie via AccessMonster.com]

I'm querying a table that has payments recorded in [payments] and the next
payment due date recorded in [duedate]. Only the most recent payment row has
a due date recorded, [duedate] is empty in the other payment rows. (there's
probably a better way to do this but i'm new).

Any query I run on these tables that includes the [duedate] field
automatically fills all the result [duedate] fields with the most recent date
entered for each customer. I don't want this and don't know why it happens.

Suggestions?

 

[John Vinson]

Any query I run on these tables that includes the [duedate] field
automatically fills all the result [duedate] fields with the most recent date
entered for each customer. I don't want this and don't know why it happens.

That sounds very odd. Please open this query in SQL view and post the
SQL text here. When you enter a new payment, do you update previous
payments to "null out" the Duedate?

Note that a due date should probably NOT EXIST in a payments table; if
it can be calculated, do so. How do you determine the due date?

John W. Vinson[MVP]

 

[Robbie via AccessMonster.com]

Thank you, John,
After posting I got frustrated and deleted all my queries to start over from
scratch. I realized I don't understand joins and probably set up some weird
conflict with that. I'm working on it and if I end up in the same place again
I'll post the SQL here.

But I was curious about your comment about not including the due date in the
payments table and maybe that's why I can't get to where I want to be. I'll
describe what I'm trying to do here and if you see this and have a suggestion
that would be great.

It's a musical instrument rental database - tables for instruments, customers,
and transactions.
The transactions (payments) table records each rental payment (linked to
customer ID and instrument ID) and the due date for the next payment (not
fixed so manually entered each time). When I enter a new payment and due date
I manually clear the due date field from the previous transaction on that
instrument. Maybe this is where I'm doing it wrong.
Anyway - what I NEED to do is at the due date send out a reminder letter
which not only reminds the customer to pay but ALSO calculates their total
payments on that instrument so far minus a percentage plus sales tax in case
they would like to purchase that instrument (rent w/ option to buy).
I can calculate all the correct amounts, etc. but I can't get it to seperate
out payments they may have made on a DIFFERENT instrument (like, if they used
to rent one but now another, or if they have two).

I realize it may be hard to answer this without knowing exactly what I've set
up so far or without basically starting me from scratch but I was curious if
my system for dealing with the due date was at the root of the problem.

Thanks to all - great site - I just found it last night while deep in despair!

Robbie

Any query I run on these tables that includes the [duedate] field
automatically fills all the result [duedate] fields with the most recent date
entered for each customer. I don't want this and don't know why it happens.

That sounds very odd. Please open this query in SQL view and post the
SQL text here. When you enter a new payment, do you update previous
payments to "null out" the Duedate?

Note that a due date should probably NOT EXIST in a payments table; if
it can be calculated, do so. How do you determine the due date?

John W. Vinson[MVP]