create a table with date

[WR Cheng]

Hi,

I am creating a table that will allow me to amortize revenue. The fields I
need for this table are:
Invoice #:
Duration:
Invoice Amount:
Aug 07:
Sep 07:
Oct 07:
......Aug 09:
Is there any ways to automate the month fields based on the duration?

Thanks

 

[Allen Browne]

Do not use repeating fields such as [Aug 07], [Sep 07], and so on.

It seems that a person can make payments against an invoice over several
months. In a relational database, this is a one to many relationship, i.e.
one invoice receives many payments. Therefore you use a related table to
handle the payments.

So your first table will have fields such as:
InvoiceID primary key
CustomerID relates to primary key of Customer table
InvoiceDate Date/Time when this started
Duration Number of months(?) expected
and so on.

Then the payment table has fields:
PaymentID primary key
InvoiceID which invoice this payment is for
PaymentDate Date/Time (the date the payment is due)
PaymentAmount Currency

 

[Jeff Boyce]

You've already received good advice about not using data (month-names) as
field names. Here's one of the reasons why:

Even if you were to find a way to "automate the month fields", you would
still need to modify any/every query that refered to these fields, and
any/every form that refered to these fields, and any/every report that
refered to these fields, and any/every ... ?Get the picture? Adding fields
to add months is how you'd have to do it in a spreadsheet, but it creates a
maintenance nightmare if you try to do it in a relational database like
Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP