Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Creating records for each day of a date range.

    Help!

    I currently have tblAcctStat with accounts and date ranges of different statuses. I need to create a maketable query to tblDailyRcds that will create a record for each day the account is in a status. The last day of the first status will overlap the first day of the next status. I'm using MS Access 2010. Below is a sample of what my original table looks like and the what the resulting table should look like:

    tbldailystat
    ACCT START END STATUS DAYS
    xyz 10/3/2011 10/11/2011 11 9
    xyz 10/11/2011 10/16/2011 13 6
    123 10/3/2011 10/6/2012 10 4
    123 10/6/2011 10/10/2011 12 5


    tblDailyRcds
    date Acct Status
    10/3/2012 xyz 11
    10/4/2012 xyz 11
    10/5/2012 xyz 11
    10/6/2012 xyz 11
    10/7/2012 xyz 11
    10/8/2012 xyz 11
    10/9/2012 xyz 11
    10/10/2012 xyz 11
    10/11/2012 xyz 11
    10/11/2011 xyz 13
    10/12/2011 xyz 13
    10/13/2011 xyz 13
    10/14/2011 xyz 13
    10/15/2011 xyz 13
    10/16/2011 xyz 13
    10/3/2011 123 10
    10/4/2011 123 10
    10/5/2011 123 10
    10/6/2011 123 10
    10/6/2011 123 12
    10/7/2011 123 12
    10/8/2011 123 12
    10/9/2011 123 12
    10/10/2011 123 12

    I tried a query trying to left join this to a table with all dates on it but I'm not getting the results I need:
    SELECT [DATE LIST].Date, [tbldailystat].START, [tbldailystat].ACCOUNTNMBR, [tbldailystat].[NEW STATUS]
    FROM [DATE LIST] LEFT JOIN [tbldailystat] ON [DATE LIST].Date = [tbldailystat].START
    GROUP BY [DATE LIST].Date, [tbldailystat].START, [tbldailystat].ACCOUNTNMBR, [tbldailystat].[NEW STATUS]
    HAVING ((([DATE LIST].Date)>=#10/1/2011#));

    Thanks in advance!

  2. #2
    Join Date
    Oct 2012
    Posts
    2
    This worked perfectly, This is a select query instead of a maketable though:

    SELECT [tblAcctStat].ACCOUNTNMBR, [tblAcctStat].[NEW STATUS], [DATE LIST].Date
    FROM [DATE LIST] INNER JOIN [tblAcctStat] ON ([DATE LIST].Date >= [tblAcctStat].START) AND ([DATE LIST].Date <= [tblAcctStat].END);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •