displaying by date

[Guest]

Dear experts!
I have spent a long time putting our old student database into access, but
what the supervisor wanted at the beginning and what he wants now are
completely different! Each student pays 24 installments which vary in price
depending on the cost of the course when they joined. So it made sense to
have 24 pairs of columns; payment date and payment amount. Now he wants to
have month/year at the top so he can skim along and see when a particular
student stopped paying at a glance. Can this be done or shall I tell him to
sod off?!

Cheers for any help you can provide.

 

[Rick B]

24 pair of columns? Date at the top?

Sounds like you are trying to create a spreadsheet. Access is a relational
database, not a spreadsheet.

Your idea of 24 pair of columns for dates and payments is not correct. You
have a classic one-to-many relationship. This should be a separate table.
Your tables should be something like...


TblStudents
StudentID
StudentFirstName
StudentLastName
StudentHomePhone
etc.

TblPayments
StudentID
PaymentDate
PaymentAmount
PaymentMethod
etc.


Each student will have one record in the TblStudents. Each payment made
will have one record in TblPayments. If a student has made ten payments, he
will have ten payments. If he has made 20 payments, he will have 20
records.

If you absolutely want to force this table to have no more than 24 records,
then you would probably need to do some fancy linking to another table with
a set 24 records. But, What is a student has a rough month and makes two
"half" payments? Or, what if they pay everything off in one big payment or
10 big payments?

You need to step back and read up on "relational database design" and "table
normalization". You are trying to create a spreadsheet in a database
application. That is generally a pretty bad idea. You don't try to create
a budget in Word, do you?

Good luck. Post back as you run into specific questions and we will do our
best to help you out.