Help with a Form

[Guest]

Hello,

I have a form with the following fields:
MemberID, First Name, Last Name, Payment Amount, Date, Payment Method,
Notes.

My problem is that the MemberID, First Name, and Last Name for a specific
person will not change however the Payment Amount, Date, & Payment Method
will change each week for the person.

How do I keep the Member ID, First Name, and Last Name the same for the
record but update the Payment Amount, Date & Payment Method each week?

Not only do I need to update the Payment Amount, Date, & Payment Method each
week for specific records but I need to keep a running total of these items
so that I can make a print out for the month. The monthly statement should
tell how much the person paid per week and the total for the month.

Thanks for the help.

 

[John Vinson]

Hello,

I have a form with the following fields:
MemberID, First Name, Last Name, Payment Amount, Date, Payment Method,
Notes.

My problem is that the MemberID, First Name, and Last Name for a specific
person will not change however the Payment Amount, Date, & Payment Method
will change each week for the person.

How do I keep the Member ID, First Name, and Last Name the same for the
record but update the Payment Amount, Date & Payment Method each week?

Not only do I need to update the Payment Amount, Date, & Payment Method each
week for specific records but I need to keep a running total of these items
so that I can make a print out for the month. The monthly statement should
tell how much the person paid per week and the total for the month.

The problem is not with your form - it's with your table design.

Starting with the Form is like building a house, windows first, and
then deciding where to pour the foundation!

If you have a One (member) to Many (payments) relationship, you need
(at least) *two* tables, related one to many:

Members
MemberID <Primary Key>
LastName
FirstName
<other bio information as needed>

Payments
PaymentID Autonumber Primary Key
MemberID <link to Members>
PaymentDate <Date is a reserved word, not good as a fieldname>
Amount
Method

For data entry you would use a Form based on Members with a Subform
based on Payments; for the report, you could use a Query joining the
two tables. Use the Report's Sorting and Grouping feature to group by
MemberID and by a calculated field to extract just the month: in a
vacant Field cell type

PaymentMonth: Format([PaymentDate], "yyyymm")

In the group Footer, you can put a textbox with a control source

=Sum([Amount])

to total the amount for the month.

John W. Vinson[MVP]