Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    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
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
  •