Results 1 to 4 of 4
  1. #1
    Banniskirk is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    4

    How can I count missing dates in a dataset

    Hello,

    I have a simple access table with a date and a quantity (inventory) entry for each day that has inventory. Days with zero inventory are not entered (data is sourced online from a list of those locations with current inventory).

    Is there a way to count the number of days where there are no entries in a record set for a given period.

    Example:

    Over a three month period the first 30 days have inventory, then none for a month and then inventory for the last month. So for the 30 days in the middle there are no entries in the table. Can I somehow count the number of days without entries?

    Thanks,

    Ken

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Your description implies you only have one entry per day, not multiple entries (i.e. different products) so you need to create a table of all the dates you are interested in and left join it to your table on the date and then count the number of records where date is null in your inventory table.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a table of dates, tDates, EVERY date.
    join this tDates to your data table,
    dbl-click the join line, set to OUTER JOIN, all records in tDates, some in your table.
    bring down DATE from both tables,
    under criteria of tYourTable.Date, set = Null

    it will show you what is missing.

  4. #4
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Take a look at this method: Seriality Control - Finding missing Numbers.
    This is about finding missing numbers. Since, date itself is internally numbers it can list out those date numbers missing in between.

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

Similar Threads

  1. Missing dates
    By valglad in forum Queries
    Replies: 5
    Last Post: 03-22-2017, 05:29 PM
  2. Missing Dates - Running Balance
    By valglad in forum Access
    Replies: 2
    Last Post: 03-10-2017, 12:20 PM
  3. Need to query for missing dates
    By Jaron in forum Queries
    Replies: 5
    Last Post: 09-11-2013, 11:58 AM
  4. Combing two tables with missing dates
    By Jerseynjphillypa in forum Queries
    Replies: 1
    Last Post: 07-19-2012, 10:51 AM
  5. Appending Missing Dates
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 05-25-2012, 09:44 PM

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