Table Problems

[ScottDevlin]

Hi Group,

Hope you can provide some assistance for me.

The problem is like this:

I basically want a relationship between constant information an
monthly variable information ie:

Constant: Account No (Primary Key??), Name, Address

Variable: Date, Rent Amount, Arrears Amount, Comments

The idea is that if user access the account then they can then selec
the month of the variable information on the same form.

Bearing in mind I want to develop the database further I want to allo
more monthly information to be added to the database, again still abl
to be retreived at a later date.

Not all accounts will have the same amount of monthly record
(Variable) as others.

What would be the solution to my problem?

:confused

 

[Steve Schapel]

Scott,

A few thoughts that might help:
It may clarify things for you if you drop the terms constant and
variable, and start to think in terms of one-to-many data
relationships. If your database is supposed to record rent payments,
then there will be a one-to-many relationship between acdounts and
payments.
You need some way to identify which account each payment relates to,
so for this purpose you will also need an Account No field in your
payments table.
Of course I have no idea about what your database is all about, but
the idea of two fields Rent Amount and Arrears Amount is probably
wrong. If you are recording payments, and then distinguishing between
whether the payment is 'rent' or 'arrears', you should have one field
PaymentAmount and another field PaymentType, and you would then store
this information in two separate records insteads of two separate
fields. If, on the other hand, 'Arrears Amount' results from a
calculation based on rent due minus rent paid, then it should not be
stored on the table, because it can be calculated from existing data
whenever you need it.
By the way, the words Name and Date have a special meaning in Access
(they are called 'reserved words') and it is not a good idea to use
them as the name of a field or control or database object.

- Steve Schapel, Microsoft Access MVP

 

[fofa]

Two tables Accounts and Rental Information???
Your Variable (Rental Information) shold contain the Account to ti
back to that table. You could then have many rows in the Variable tie
to one Account. Unless I don't understand the question

 

[fofa]

Two tables Accounts and Rental Information???
Your Variable (Rental Information) shold contain the Account to tie
back to that table. You could then have many rows in the Variable tied
to one Account. Unless I don't understand the question.