Results 1 to 2 of 2
2008-03-03, 22:04 #1
- Join Date
- Nov 2007
- Sacramento, California, USA
- Thanked 0 Times in 0 Posts
general format for database (access 2003)
I am building a budget/purchase tracking data base. Ultimately I have budget information, purchase order info, and invoice info that needs to be tracked. This will cover several fiscal years. I am having a hard time figuring out how to handle the FY aspect. For example, there will be times that purchase orders and or invoices will have monies associated with more than one FY.
At this point I have fields called "amount0607" "amount0708" etc. Am I on the right track?
2008-03-03, 22:12 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: general format for database (access 2003)
I wouldn't use different fields for this - the number of fields needed would gradually increase over the years, making the table difficult to handle.
I would use a separate table instead for each table that needs to assign amounts to different fiscal years, with the following fields:
- An ID field that links to the parent table (for example to the purchase orders table).
- A field that specifies the fiscal year.
- A currency field for the amount.
If purchase order # 37 has amounts for 2006/2007 and 2007/2008, there would be two records for purchase order #37 in this table, one for 2006/2007, and one for 2007/2008.
This setup makes it easy to summarize amounts by purchase order but also by fiscal year.