Summarize transations amts on the "Master" record.

Hi,

I’m using Access via XP Access Pro running in XP Pro with SP3. Please note
that this question was posted in both the Database design and Form coding
forums (if I did it right).

I am donating my services to a homeless charity. They have about 80 homes
and apartment builds that they rent, at a greatly reduced rate, to people who
are rebuilding their lives. Most people stay in the homes for a year or less.
Currently, they are tracking all of the information on an Excel spreadsheet.
I’ve been asked to put this into an Access database. Expected transaction
count per renter is about 36 transactions per year with approximately 160
renters per year or approximately 5800 transactions per year.

My tables a

Building – Keyed by building name. This will contain the name of all of the
physical buildings. There will be a code to tell me if this is a home or
apartment.

Unit – Keyed by building name and apartment number. This will contain all
of units that are in the building.

Renters – Keyed by auto number and indexed by name. This will contain the
renter’s information. I don’t know if I will have a Lease table or if I will
combine it with the renter’s information.

Transaction - Keyed by auto number and indexed by renter’s number from the
Renter’s table. The transactions will be Deposit, Rent, Late Fee, NSF
Charge, Misc Fee, and Adjustment. There will be a “Due� and “Paid� version
of each of the preceding transactions.

Our main objective with this project is to be able to have a report and
inquiry screen that shows how much each person owes as of the current minute.
There are

Here are my questions:

I’m still new to Access and I’ve never written anything with a “Transaction�
file where I have to summary the results in a Master file. I have downloaded
the Inventory Managements template from Microsoft. Does anyone know if there
is a better example for what I want to do?

Are there any suggestions or traps that I might want to avoid or hints to
assist me in efforts?

Thank you for your assistance.


--
Dennis

I don’t know if I will have a Lease table or if I will combine it with the
renter’s information.
Use one table with field to identify Renter/Leaser. Need a field for
Inactive – Yes/No. Need to add fields to set payment schedule – Period (d-
Daily, w- Weekly, b- Bi-weekly, m- Monthly)
Unit needs a Autonumber field as primary key.
All transactions to use a combo to select type from list/table. Charge use
a Currency field. Need Note/Comment to define Misc Fee and Adjustment.
Use an Append query to add new Rent transaction record for all active
renters when the date is past the Maximum Due date. Query would use Period
to determine next Due date – DateAdd([Renter].[Period], 1, Max([Due])) to
add. Use last Charge for new record.
Others might suggest how to record partial payments.

--
Build a little, test a little.


"Dennis" wrote:

Hi,

I’m using Access via XP Access Pro running in XP Pro with SP3. Please note
that this question was posted in both the Database design and Form coding
forums (if I did it right).

I am donating my services to a homeless charity. They have about 80 homes
and apartment builds that they rent, at a greatly reduced rate, to people who
are rebuilding their lives. Most people stay in the homes for a year or less.
Currently, they are tracking all of the information on an Excel spreadsheet.
I’ve been asked to put this into an Access database. Expected transaction
count per renter is about 36 transactions per year with approximately 160
renters per year or approximately 5800 transactions per year.

My tables a

Building – Keyed by building name. This will contain the name of all of the
physical buildings. There will be a code to tell me if this is a home or
apartment.

Unit – Keyed by building name and apartment number. This will contain all
of units that are in the building.

Renters – Keyed by auto number and indexed by name. This will contain the
renter’s information. I don’t know if I will have a Lease table or if I will
combine it with the renter’s information.

Transaction - Keyed by auto number and indexed by renter’s number from the
Renter’s table. The transactions will be Deposit, Rent, Late Fee, NSF
Charge, Misc Fee, and Adjustment. There will be a “Due� and “Paid� version
of each of the preceding transactions.

Our main objective with this project is to be able to have a report and
inquiry screen that shows how much each person owes as of the current minute.
There are

Here are my questions:

I’m still new to Access and I’ve never written anything with a “Transaction�
file where I have to summary the results in a Master file. I have downloaded
the Inventory Managements template from Microsoft. Does anyone know if there
is a better example for what I want to do?

Are there any suggestions or traps that I might want to avoid or hints to
assist me in efforts?

Thank you for your assistance.


--
Dennis

Karl,

Sorry, I was not clear. The Renter table would contain the names of the
people who rent or lease the apartment. Based on your comment, I should
change the name of the table to Contract table. The Contract table would
contain the details of the lease or rental contract. It is unlikely, but
possible that a renter could move from one facility to another within their
year in the program. The Lease table would allow me to track each rental
agreement they had.
--
Dennis


"KARL DEWEY" wrote:

I don’t know if I will have a Lease table or if I will combine it with the
renter’s information.
Use one table with field to identify Renter/Leaser. Need a field for
Inactive – Yes/No. Need to add fields to set payment schedule – Period (d-
Daily, w- Weekly, b- Bi-weekly, m- Monthly)
Unit needs a Autonumber field as primary key.
All transactions to use a combo to select type from list/table. Charge use
a Currency field. Need Note/Comment to define Misc Fee and Adjustment.
Use an Append query to add new Rent transaction record for all active
renters when the date is past the Maximum Due date. Query would use Period
to determine next Due date – DateAdd([Renter].[Period], 1, Max([Due])) to
add. Use last Charge for new record.
Others might suggest how to record partial payments.

Karl,

I like your idea about the Append query. I have not though of that. I was
going to write a batch program, but your idea is much simpler.

Dennis


--
Dennis