Monthly Progression

[tcappie]

I am building a database in which I will be keying in monthly expenses for
projects. Each month each project will build expenses indefinitly. I am
listing my projects on one table and then the expenses on another. There are
five types of expenses for each project so I need to be able to do this
monthly. Is there a way I can auto progress months? Ideally the table would
have January 09, February 09, March 09, and so on indefinitly so I can track
the 5 expense fields for each months expense on each project.

 

[Allen Browne]

If you are considering different months as fields, that's not a normalized
design.

Just use a date field, so your table will have fields like this:
- ProjectID relates to your table of projects
- ExpenseDate date of this expense
- ExpenseAmount dollars

There may be other fields (such as an ExpenseCategoryID that says what that
expense is for, or a Text field where you can key in a random description
for the expense.)

If you only want to store values for the month (not the specific date), use
the first of the month as the date. You can format the field as:
yyyy-mmm
so it displays 2009-Jan (even though it actuall stores 1/1/09.)

With the data stored like that, you can get a monthly summary report, you
can group by month, and you can even use a crosstab query if you want to
view the data with a month for each column.