If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
create a table with date
Hi,
I am creating a table that will allow me to amortize revenue. The fields I need for this table a 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 |
#2
|
|||
|
|||
create a table with date
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "WR Cheng" wrote in message news Hi, I am creating a table that will allow me to amortize revenue. The fields I need for this table a 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 |
#3
|
|||
|
|||
create a table with date
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 "WR Cheng" wrote in message news Hi, I am creating a table that will allow me to amortize revenue. The fields I need for this table a 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 |
Thread Tools | |
Display Modes | |
|
|