Results 1 to 5 of 5
  1. #1
    MJD is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    2

    Running Count Query Help

    I have an table...

    Installs

    Id Timestamp City

    1 2017-04-14 12:00 Lisbon
    2 2017-04-15 02:00 York
    3 2017-04-17 16:00 Perth
    4 2017-04-17 18:00 Ohio


    1 2017-04-18 22:00 Dundee


    I need a query that gives me installs to date for each day...

    Date Installs To Date

    2017-04-14 1
    2017-04-15 2
    2017-04-16 2
    2017-04-17 4
    2017-04-18 5

    (But I don't want to use domain functions!)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    you can make 2 queries:

    Q1: (to get your format)
    select format(timestamp,"yy-mm-dd") as InDate from table

    Q2:
    select InDate, Count(InDate) from Q1

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    There is no record for 2017-04-16 in the source so there cannot be that date in the output unless you have a table of all dates that can be joined with in the query. Or create a dummy record with city as "None".

    Running sum in query is not simple and may be impossible without domain aggregate function. However, this is simple in a report because textbox has RunningSum property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    MJD is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    2
    This gives the total installs on each day. I need a running total of installs to that date.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This can only be done in VBA due to the 16th not being an actual record. A loop from a beginning date and adding 1 to it each time until it gets to an end date. For each day you will need to run a query (if you don't want to use domain counts) and add a record to a temporary table with the values.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-08-2017, 05:52 PM
  2. Replies: 6
    Last Post: 11-05-2014, 03:00 PM
  3. Replies: 5
    Last Post: 06-11-2013, 12:11 PM
  4. Running page count tied to volume number
    By bmj121 in forum Programming
    Replies: 5
    Last Post: 04-27-2012, 11:24 AM
  5. Running Count Query
    By monkey2003 in forum Queries
    Replies: 0
    Last Post: 09-21-2009, 12:24 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