How to enter contributions records for different years. E.g 2010 to present

Question

I am creating an MS Access file for welfare contributions for my local church. I have created a table to record the details of the contributions which includes, payment date, contribution month, contribution year, etc. I entered data for 2017 for example and saved it and inputed data for a different year. My challenge is, whenever I enter info via the form for a particular year say 2017 and try to enter data for another year (2018) after saving, data for the 2018 overwrites data for the 2017. I need assistance.

Answer 1

The key to creating a reliable and robust relational database is to get the physical model, i.e. the set of tables and relationships between them, correct so that it models the reality correctly.

In your case you'd need tables, in broad outline, along the following lines. I'm assuming that the payment date might not necessarily reflect the contribution year and month

Contributors ….ContributorID (PK) ….FirstName ….LastName

Contributions ….ContributionID (PK) ….ContributorID (FK) ….Amount ….ContributionYear ….ContributionMonth ….PaymentDate

If the PaymentDate does reflect the contribution year and month you can dispense with the ContribtionYear and ContributionMonth columns (fields). The year and month can easily be derived from the date, so to include columns for them would introduce redundancy, and the table would not be normalized to Third Normal Form (3NF), leaving it open to update anomalies.

For data entry of each contribution the contributor would be entered in a form bound to the Contributions table by means of a combo box set up as follows:

ControlSource: ContributorID

RowSource: SELECT ContributorID, FirstName & " " & LastName FROM Contributors ORDER BY LastName, FirstName;

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches. The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource: SELECT ContributorID, LastName & ", " & FirstName FROM Contributors ORDER BY LastName, FirstName;

However, selecting a person purely by name is not really satisfactory, as personal names can be duplicated (I worked with two Maggie Taylors). To differentiate between them, further details can be shown concatenated to the name, e.g. the contributor's address.

If the PaymentDate does reflect the contribution year and month you'd include two unbound text boxes in the form to show the year and month, with the following expressions as their ControlSource properties:

= Format(ContributionDate, "mmmm")

= Year(ContributionDate)

If you do include ContributionMonth and ContriButionYear columns in the table, for the former I'd use a combo box set up as follows:

ControlSource: ContributionMonth

RowSourceType: Value List

RowSource: 1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0cm

The ContributionMonth column should be a number data type. The months would then be stored by their month number, which makes processing the data easier.

I'm not sure where you are going wrong at the moment, but a form set up as above would avoid any data loss, provided that each new contribution is entered into an empty new record.