Results 1 to 2 of 2

Thread: Dsum Help

  1. #1
    Join Date
    Oct 2008
    Location
    Rochester, Ny
    Posts
    3

    Unanswered: Dsum Help

    I have a database with the following 2 tables:

    Table Name: Capital Lease Info
    Store Depreciation Fiscal Year
    120 $1,000 2009
    120 $900 2010
    120 $800 2011
    120 $700 2012
    220 $1000 2009
    220 $900 2010
    220 $800 2011
    220 $700 2012
    ETC.....

    Table Name: Capital Lease Net Book Value at 3/08
    Store Net Book Value
    120 $50,000
    220 $40,000
    ETC....

    I want a query where I can enter a "Fiscal Year" and return the "Net Book Value at that time for each store in the "Capital Lease Info Table".

    Example:
    The Net Book Value for Store 220 at the end of Fiscal Year 2010 should be $43,100 (NBV at 3/08 $45,000 less ($1,000) 2009 and ($900) 2010).

    Any help would be appreciated.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You do not need to use the DSUM method. Using two queries will give you the required result. The first query totals the depreciation for each year with an <=[Enter Fiscal Year] in the criterai for the Fiscal year. This will give you the total for each store for the Fiscal Year(s) <= to the year entered. Now in the second query add the NBV table fields store and the first query then link the store in the table and query. Also put [Enter Store] in the criteria for the store. To obtain the result deduct the total from the total in query 1 from the NBV.

    I have attached an example.
    Attached Files Attached Files

Posting Permissions

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