Results 1 to 3 of 3
  1. #1
    Wobbler is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    2

    Data Manipulation


    Hi,

    Fairly limited experiecne in access but dabble here and there. Reasonable knowledge of tables, forms, queries etc and done a bit of VBA.

    I have a task where I have a list or projects and each project can have a number of date ranges associated with it. For example:

    Project A
    Range1: Start Date 20/06/2020 End Date 22/06/2020
    Range2: Start Date 24/06/2020 End Date 26/06/2020

    Project B
    Range1: Start Date 19/06/2020 End Date 22/06/2020

    I would like to have a query that lists each date from today for 3 months and for each day counts the number of projects that have the date in their range:

    For Example (considering above):

    18/06/2020: 0
    19/06/2020: 1
    20/06/2020: 2
    21/06/2020: 2
    22/06/2020: 2
    23/06/2020: 0
    24/06/2020: 1
    25/06/2020: 1
    26/06/2020: 1
    27/06/2020: 0
    28/06/2020: 0

    I have a significant larger number of projects and date ranges for each project but im strugling to understand what would be my best approach. So far ive had a look at doing through a query.

    Any advice offered would be great.

    Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    1. Need a dataset of all dates for 3 months. This can be produced by a query but it is a very tricky, very slow query. So maybe use VBA to populate a 'temp' table with appropriate dates when procedure is run (purge records at beginning) or maintain a table of dates and add records as needed and apply filter.

    2. You show international date format. This can be an issue when sorting and filtering dates. Review http://allenbrowne.com/ser-36.html

    3. Options to calculate count:

    SELECT Dates.Dte, (SELECT Count(*) FROM Projects WHERE Dates.Dte Between StartDate And EndDate) AS Cnt FROM Dates;

    SELECT Dates.Dte, DCount("*","Projects","#" & [Dte] & "# Between StartDate And EndDate") AS Cnt FROM Dates;

    or a VBA custom function

    Code:
    Function GetCnt(dte As Date) As Integer
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Projects")
    Do While Not rs.EOF
        GetCnt = GetCnt + IIf(dte >= rs!StartDate And dte <= rs!EndDate, 1, 0)
        rs.MoveNext
    Loop
    End Function
    

    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.

  3. #3
    Wobbler is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2020
    Posts
    2
    Thanks. Ill take a look at this and see how I get on.

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

Similar Threads

  1. Data Manipulation
    By Thompyt in forum Programming
    Replies: 7
    Last Post: 03-27-2015, 05:32 PM
  2. regarding manipulation of data in combobox
    By ritimajain in forum Forms
    Replies: 11
    Last Post: 08-01-2013, 03:03 AM
  3. Date data manipulation
    By Duncan in forum Access
    Replies: 3
    Last Post: 03-09-2012, 11:13 PM
  4. Access DATA Manipulation issue
    By humanmaycry in forum Access
    Replies: 2
    Last Post: 07-25-2011, 08:28 PM
  5. Form output data manipulation
    By bearsgone in forum Forms
    Replies: 1
    Last Post: 04-05-2011, 04:21 PM

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