Results 1 to 3 of 3
  1. #1
    Jame1212 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    1

    How to model a time period not a certain point of time? Problem

    Hello,



    I am currently writing a thesis for my university in cooperation with a media-company. I built a real estate database in MS-Access. I try to create dashboards based on this database, but the numbers created are wrong, because Access does not unerstand that a contract runs for a period of time.

    Example:
    Imagine 2 lease contracts.
    The first one runs from 1st of April 2016 to the end of 2017. Monthly lease expense 2000 USD
    The second one runs from the beginning of 2013 until the end of 2020. Monthly lease expense 2020 USD

    In my table I have the attributes:
    ContractID (primary key) (auto number)
    LeaseFrom (date)
    LeaseUntil (date)
    MonthlyLeaseExp (currency)

    So I have the data:

    1 01.04.2016 31.12.2017 2000
    2 01.01.2013 31.12.2020 2000


    When I now go to Power BI, a Microsoft provided service for building dashboards, and I set filters:

    Lease from = is 2016
    Lease until = is smaller than 2017

    in order to show the lease contracts in 2016 with their montly lease expense.

    The answer I get is:

    Lease contract 1 = 2000 USD

    But lease contract 2 is not shown. I guess its because Access only sees 2 points in time. In case number 2, 2013 and 2020, which is not 2016. However logically this lease contract is valid in 2016 and I want this to be shown.

    What do I have to do to model a time period? I would be really thankful for answers, ideas etc.

    Kind regards

    Jannik

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure about power BI, but in a query you would have something like

    SELECT *
    FROM tblLeases
    WHERE LeaseFrom<=#12/31/2016# and LeaseTo>=#01/01/2016#

    which will show all leases which were active at some point during 2016 (may be all year or just part of a year)

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just a small comment re this statement
    because Access does not unerstand that a contract runs for a period of time.
    Access and other software involving logical constructs does what you tell it ---consistently.

    So, not knowing anything about M$oft BI, it seems to be an issue with some logic either in BI or your table or.....???

    But with no corruption and no other instructions, my guess is that Access is doing what it has been told.

    If it's any consolation---we've all been there.
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I store a period of time
    By OscarCat in forum Access
    Replies: 2
    Last Post: 06-10-2012, 05:08 PM
  2. Set Database to Expire in time period
    By robsworld78 in forum Access
    Replies: 12
    Last Post: 06-07-2011, 06:54 AM
  3. Count Items by Time Period
    By pawslover in forum Queries
    Replies: 1
    Last Post: 11-15-2010, 03:57 PM
  4. Managing Point in Time
    By Stressed in forum Database Design
    Replies: 1
    Last Post: 03-09-2010, 07:55 AM
  5. Managing Point in Time
    By Stressed in forum Programming
    Replies: 0
    Last Post: 03-01-2010, 08:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums