Results 1 to 6 of 6
  1. #1
    RDT0086 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    6

    Create Time period query

    I am ultimately trying to create a new table through querying data from an existing table. The new table will define time period and provide a count for the number of records from the existing table based on dates.

    Existing table looks like.

    Click image for larger version. 

Name:	table.png 
Views:	11 
Size:	4.3 KB 
ID:	23617
    EIN Start Date Termination Date
    1 1/1/2009 6/7/2014
    2 5/5/2009
    3 2/5/2010 6/12/2015
    4 5/19/2012
    5 6/13/2014 12/5/2015





    I need the new table to look like (sorry it is long) Need some help in getting this done:


    Period Active Termed
    Jan-09 1
    Feb-09 1
    Mar-09 1
    Apr-09 1
    May-09 2
    Jun-09 2
    Jul-09 2
    Aug-09 2
    Sep-09 2
    Oct-09 2
    Nov-09 2
    Dec-09 2
    Jan-10 2
    Feb-10 3
    Mar-10 3
    Apr-10 3
    May-10 3
    Jun-10 3
    Jul-10 3
    Aug-10 3
    Sep-10 3
    Oct-10 3
    Nov-10 3
    Dec-10 3
    Jan-11 3
    Feb-11 3
    Mar-11 3
    Apr-11 3
    May-11 3
    Jun-11 3
    Jul-11 3
    Aug-11 3
    Sep-11 3
    Oct-11 3
    Nov-11 3
    Dec-11 3
    Jan-12 3
    Feb-12 3
    Mar-12 3
    Apr-12 3
    May-12 4
    Jun-12 4
    Jul-12 4
    Aug-12 4
    Sep-12 4
    Oct-12 4
    Nov-12 4
    Dec-12 4
    Jan-13 4
    Feb-13 4
    Mar-13 4
    Apr-13 4
    May-13 4
    Jun-13 4
    Jul-13 4
    Aug-13 4
    Sep-13 4
    Oct-13 4
    Nov-13 4
    Dec-13 4
    Jan-14 4
    Feb-14 4
    Mar-14 4
    Apr-14 4
    May-14 4
    Jun-14 5 1
    Jul-14 4
    Aug-14 4
    Sep-14 4
    Oct-14 4
    Nov-14 4
    Dec-14 4
    Jan-15 4
    Feb-15 4
    Mar-15 4
    Apr-15 4
    May-15 4
    Jun-15 4 1
    Jul-15 3
    Aug-15 3
    Sep-15 3
    Oct-15 3
    Nov-15 3
    Dec-15 3 1

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Your current table has 6 rows/records. While your new (proposed) table has 60. Thus you must create records - - and you did so manually to make this post. Because of course in example of February 09 there is currently no record. So do you need to further this via query set up or do you want to just accept the manual effort by copying your post into a new table? How many new records ultimately are involved? ...and is this a 1 time thing or does it need to grow automatically?

  3. #3
    RDT0086 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    6
    I would eventually like the table to grow over time, but that is not as important. For example, once this month is over it would add data for Feb-16. For now I am ok with a manual process for the period columns, but I do not what to calculate the active or terms for each period manually.

    I did try to create a table manually that had the period range from Jan-09 to Dec-25 with additional columns of StartofPeriod and EndofPeriod to define the period dates, and then try to somehow use both tables to create a new table via a query. I failed at this as I do not know how to link the tables to create a new one. Normal joins would require that the dates matched exactly while I am looking for an aggregated version.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    well it is easy to do a sum based on any date - say 6/6/09; you sum the count of Start Dates and sum the count Term Dates - and take the difference. So in terms of a query where one could plug in any date and get the number it is fairly easy to do.

    but what you seek is a running sum across arbitrary periods (period as month is arbitrary since it could be week, biweek, quarter,etc). So you do need your Period Table constructed. But 'Period' is just text so you must add 2 new real date fields: StartDate, EndDate and populate those as well so that 'Period' has a real definition via date fields. Then you need to join this Period Table in a query to the data with a complex double join between the date fields. I call it complex because you'll need to use SQL view in order to manipulate the join definition so rather than its default (=) you can have it be => or =<. You need to group the data records by period correctly, and then do counts. Plus you need to duplicate this for the Start and Term columns.

  5. #5
    RDT0086 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    6
    stbl_PERIOD.zipWell that makes me feel better as that was the path I took originally, but I could not get the sql syntax to work in correctly creating the join structure needed. I do not use access all that much, but I am very familiar with MySQL and VBA in Excel. I did load the date in a SQL database that I have and was able to do it correctly there, but that SQL code does not seem to work in Access. I can provide a sample later today of what I tried (this is for a work project and I mirrored it on my home computer last night where I have apache running so I will have to look at it when I get home)

    If you have time I would appreciate some advice on the SQL code I should use to create the join. I still have the table I created for the periods and I have attached it here in excel format.

    Thanks for the help!

  6. #6
    RDT0086 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    6
    I was able to solve this using VBA...but I am still not sure why the SQL code that worked on my SQL DB did not work on this MS Access database

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

Similar Threads

  1. running total time period
    By dadaboss92 in forum Queries
    Replies: 3
    Last Post: 05-22-2015, 09:16 AM
  2. Replies: 9
    Last Post: 09-17-2014, 05:19 AM
  3. Replies: 2
    Last Post: 10-30-2013, 11:40 AM
  4. Query for Specific 24 hour time period
    By esh112288 in forum Queries
    Replies: 1
    Last Post: 10-23-2012, 02:16 PM
  5. How do I store a period of time
    By OscarCat in forum Access
    Replies: 2
    Last Post: 06-10-2012, 05:08 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