want to automatically generate records

[4charity]

Trying to figure out the best way to do this.
I have client records that are compiled into monthly reports, which show how
much housing assistance was given per client. The report lists basic client
info, landlord, $amount dispersed, #bedrooms, and then an adjustment amount
and memo (these 2 are only occassionally used.)

I have created a form/subform.
Form: Basic Client Info/Landlord
Subform: Autodate (NOW), Amount, Bedrooms, Adjustment, Memo.

Most of the information stays the same from month to month, unless a yearly
lease is renewed, and the Amount or Bedrooms change. The adjustment would be
added once in awhile, if an extra bill was paid.

I thought I would have the subform autogenerate the same data each month,
and then have all of the data in an Append Table Query, so that a record
would be kept of what was actually paid each month. But can I get Access to
automatically generate a new record, copying all of the old data (except for
the new autodate) in the subform? I have no clue on how to do this, it just
seemed like one way to skin a cat.

If anyone has the solution on how to do this, it would be much appreciated.
Or, a bettter way of doing it.

There are 3 tables involved - Clients, Landlord, Payment.

Right - sometimes the landlord changes midstream, too.

Thx.

 

[Arvin Meyer [MVP]]

You can probably calculate the results without building a meaningless
record. I'd just add a note to a memo field in another table using the
ClientID. If there are 12 monthly payments, you take the amount and multiply
it by 12 for a yearly report. If the rent changed in July, another entry
would change the calculation. I'd include a start and an end field in the
payment table and use that to make the calculations the entries might look
like:

ClientID Amount StartDate EndDate
123 $850 1/1/09 7/31/09
456 $675 1/1/09 12/31/09
123 $965 8/1/09 12/31/09

By using alias columns in your query yo calculate

123 $850 7 $5950
456 $675 12 $8100
123 $965 5 $4925

Then use a Totals query as a source for your report and get:

123 12 $10,875
456 12 $ 8,100

 

[Clifford Bass]

Hi,

You may wish to consider a more specific structure that does not
require repeating the same information each time. Here is one possible
starting structure, subject to modification as needed:

tblClients
ClientID - Primary Key (PK)
LastName
FirstName
etc.

tblLandlords
LandlordID - PK
Company
LastName
FirstName
etc.

tblUnits
UnitID - PK
Address
Bedrooms
other

If you deal with units that are remodeled and the information you are
tracking can change, just add a new row. You could include start and end
dates in this table.

tblUnitLandlords
UnitID - PK field 1
LandlordID - PK field 2
other

If you deal with the sale of properties to new landlords, you might
include a starting and an ending date in this table, with the starting date
as part of the primary key.

tblTransactions
TransactionID - PK
ClientID
UnitID
TransactionDate
TransactionAmount (+ for payments; - for credits)
Notes (Don't use "Memo" as it is a reserved word.)
other

Then you only need to specify a client and a unit and the transaction
information. In your queries, you can make the connections between the
clients, the unit information and the landlords.

Hope that helps,

Clifford Bass