Results 1 to 3 of 3
  1. #1
    medusa3604 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    9

    Building a database a table thats showing active records day by day.

    Hi,

    I have a linked table by ODBC that contains records with a type field, start date and end date.

    What I would like to do is to have Another table which I create with a full year, one record is one day. And one field called number of active records.

    Then I would like that table to look at the records from the ODBC source to see how many records that was Active on each day in the year table.

    If start date less than date and end date greater than date then plus 1. So I would get a sum in the number of active records.



    I want to use this as a overview in a diagram.

    Or is there Another way of doing a similar thing?

    Anders
    Sweden

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Create an append qry to count the records in the table by date. (Sum or count)
    then to fill in the missing dates, I would write a vb code to append all the dates between start and end.
    Code:
    VDate = startdate
    while vDate <= EndDate
       SSql = "insert into table ([DATE]) values (#" & vDate & "#)"
       Docmd.runSql sSQL
    
        VDate = dateAdd("d", 1, vDate)
    wend

  3. #3
    medusa3604 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    9
    Than you for your answer.

    I´m not sure I follow on how to do this with an append Query. Wouldn´t I just get the start and end dates? Not the sum of Active records?

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

Similar Threads

  1. Replies: 7
    Last Post: 04-02-2015, 07:25 AM
  2. Building a single variable value of different records
    By stephenaa5 in forum Programming
    Replies: 7
    Last Post: 01-29-2015, 08:36 PM
  3. Replies: 12
    Last Post: 10-01-2014, 08:17 PM
  4. Active Records Vs Inactive Records
    By ClawGee in forum Database Design
    Replies: 4
    Last Post: 05-07-2012, 08:13 PM
  5. Replies: 1
    Last Post: 03-09-2011, 02:04 AM

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