Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    Oct 2008
    Rochester, Ny

    Unanswered: Query Help

    I have a database that has two tables, "Master Lease Schedule" contains a list of all our locations indicating a bunch of different information below I will detail the information I need to use. The other table "Rent By Month" indicates the monthly rental amounts for each store in the "Master Lease Schedule" See Below for examples

    Master Lease Schedule
    Store 1st Term 1st Term 2nd Term 2nd Term 1st Term 2nd Term
    Num. Beg. End Beg. End Total Rent Total Rent
    2 4/1/2008 3/31/2013 4/1/2013 3/31/2018 $200,000 $250,000
    5 4/1/2008 3/31/2018 4/1/2018 3/31/2028 $550,000 $600,000

    Rent By Month
    Store # 4/1/2008 5/1/2008 6/1/2008 7/1/2008 8/1/2008 Etc...
    2 $3333.33 $3333.33 $3333.33 $3333.33 $3333.33
    5 $4583.33 $4583.33 $4583.33 $4583.33 $4583.33

    This schedule continues on showing monthly rental amounts for all stores contained in the Master Lease Schedule going out until 4/1/2050.

    What I need to build is a query that will show me Total rent by fiscal year by store and look like the following.

    "Rent By Fiscal Year"
    Store # FY # FY Start Date FY End Date Total Rent for FY
    2 2010 4/1/2009 3/31/2010 $xx,xxxx
    2 2011 4/1/2010 3/31/2011 $xx,xxxx
    2 2012 4/1/2011 3/31/2012 $xx,xxxx
    5 2010 4/1/2009 3/31/2010 $xx,xxxx
    5 2011 4/1/2010 3/31/2011 $xx,xxxx
    5 2012 4/1/2011 3/31/2012 $xx,xxxx

    I would like to be able to enter FY start date and return the total rent for each of the next five fiscal years, with all remaining years added together

    Example enter a start date of 4/1/2009 and I would get total rent by store for FY2010, 2011, 2012, 2013, 2014 and then FY 2015-FY 2050

    I can do this in excel easy enough, but I would like it in access any ideas would be greatly appreciatied I can figure it out.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    first step.... normalise your data
    Rent By Month
    Store # 4/1/2008 5/1/2008 6/1/2008 7/1/2008 8/1/2008 Etc...
    2 $3333.33 $3333.33 $3333.33 $3333.33 $3333.33
    5 $4583.33 $4583.33 $4583.33 $4583.33 $4583.33

    truly becomes RentByMonth

    2 01/04/2008 $3333.33
    2 01/05/2008 $3333.33

    having normalised the data the fiscal year queries becomes easier
    sum all values for that came in between satrt date and end date
    you could also do comparatives year by year, quarter by quarter etc....

    if you had other info on the properties you could do comparatives based on other criteria (eg floor area or whatever else it is that commercial agents ort landlords get excited about)

    of course the other technique may be to store the data in Access and then dump it into Excel and let someone paly with the nuumbers to their hearts content
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2007
    Washington D.C.
    I also work with fiscal years quite a bit.... for what it's worth... one technique that I use in all of my databases is a reference table that has the calendar date as one field and then the fiscal year as the other field.

    Assuming that your fiscal year is the same as the govt's.... (October 2008 - September 2009 = Fiscal Year 2009), this method works pretty well for me.

    Wasn't aware of an excel query that would calculate the FY, but the way I figure it, if I have a table that I pre-populate ahead of time going all the way out to say 2020, then I don't have to rely on the computer do do any calculations.

    It then it would just be a matter of creating a relationship between the calendar date in your rent table to the calendar date in the reference table.

Posting Permissions

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