Results 1 to 2 of 2
  1. #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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts