MS Access grouping query spanning start and end dates

0
votes

I would like to get a running tally of how many widgets were/are rented at any one time, by month, by year. Data is held in an MS Access 2003 db;

  • Table name: rent_table
  • Fields:
    • rentid
    • startdate
    • enddate
    • rentfee
    • rentcost
    • bookingfee

Something like; Count number of rentid's that fall between month/year, then group them?

e.g. if a widget was rented from 5th Jan 2014 to 8th April 2014 it would appear as a count in Jan, Feb, Mar and April tally's.

Many thanks.

EDIT

More details (sorry);

  1. Access db is fronted by classic ASP.
  2. If possible I don't want to create any new tables.
  3. No input is required in order to run the report.
  4. There are around 350-400 widgets that could be rented at any one time.
  5. Each widget is rented exclusively.

  6. Report output example;

    • Month | Year | NumRented
    • Jan 2014 86
    • Feb 2014 113
    • ...

Can a query pick up dates within dates? So literally do a count of the table where date >Dec 31st 2013 AND <1st Feb 2014 (to grab a count for all of January 2014) and would that include the example of the rent starting on the 5th Jan? So I could just do twelve counts for each year?

1
sqlms-access
This could get complicated, do you just want a table rented on Jan 5th to count as 1, or would you like it to be a fraction of one? How do you want to present this report? A pivot table? An access report? How do you want to generate this report? Do you want a user to select a month and provide them a count? Do you want to show a whole year in a snap shot and a sum of how many rentals there were per month? We really need a lot more information to help you with this – pegicity
Hahaha, just realized I used the word "Table" instead of "widget" a few times, but you get what I am looking for – pegicity

1 Answers

0
votes

  1. create a calendar table, e.g. table = cal_yyyymm with one column dt_yyyymm as numeric field

  2. populate the table with ... say 5 or 10 years of data 201401 201402 201403 ... 60 or 120 rows, a small table

  3. make a sql 

    Select
    dt_yyyymm,
    count(*) as cnt

From  cal_yyyymm

Left Join  rent_table
    On  format(startdate,"yyyymm") >= dt_yyyymm
    And dt_yyyymm  >= format(enddate,"yyyymm")

  4. think about the complications in the data -- --

    widget was rented from 5th Jan 2014 to 8th Jan 2014 and again rented from 11th Jan 2014 to 21st Jan 2014 does this count at 1 or 2 in the month?

    if it is 1, then the sql gets more complicated because the rent_table first needs to have its dates converted to yyyymm format, and second needs to be de-duped on rentid, and third then joined to cal_ On the dates...