Results 1 to 11 of 11
  1. #1
    NoviceAccess is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Location
    Houston
    Posts
    6

    Automatically Insert rows with Days in Months

    Hi All,


    I am a novice in terms of access. I have managed to create a simple database with forms and queries that enables others without any knowledge of access to use my database.
    In the database, I have a table with Months of the year with projected production daily volumes for each month.
    example:

    Month Projected Production
    January 100
    February 200
    March 300
    ............ so on until December

    How can I make a query to auto populate all the days of the year with the following output :

    Date Projected Production
    1/1/2016 100
    1/2/2016 100
    1/3/2016 100
    ........
    2/1/2016 200
    2/2/2016 200
    ..........
    and so on.

    I need the data in the above mentioned format(details) for further analysis. My database looks cool as I use only forms.However, the problem mentioned above is a big challenge in my efforts to automate the database.

    I will greatly appreciate the help from the experts in the forum.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In a table with your values, the code below will get a record, run an append query for each day in that month.
    Code:
    sub btnRun_click()
    dim rst.
    dim iVol as integer
    dim vDate,vMo,vEndDate,vStartDate
    
    Docmd.setWarnings false
    set rst=currentdb.openrecordset("select * from tSettings")
    with rst
       While not .eof
           VMo=.fields("month").value
            VStartDate=vMo & "/1/" & txtYR
            VVol =.fields("volume")
            VEndDate=dateAdd("d",-1,dateAdd("m",1,vStartDate))
             VDate= StartDate
    
            While vDate <= vEndDate
                 SSql = "insert into table ([date],[volume]) values (#" & vDate & "#," & vVol & ")"
                Docmd.runSql sSql 
                  vDate= dateAdd("d",1,vDate)
             Wend
    
            .movenext
       Wend
    end with 
    set rst =  nnothing 
    end sub

  3. #3
    NoviceAccess is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Location
    Houston
    Posts
    6
    Hi ranman256,
    Looks like it is a VBA code.
    I tried to use it but have no idea how to use VBA in Access. Also, where do I use my table name in the code. Sorry, I am a novice in programming let alone VBA.
    Thanks for your time.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Attached is an example dB.

    Open the dB.
    Press (ctl+G) to open the VBE editor
    Open "Module1". (double click on the name)
    Open the table "tblProjProd". It is empty.
    Close the table
    Go to the VBE editor.
    Click anywhere in the procedure, then press the "F5" key.
    Open the table "tblProjProd".

    ================================================== =======

    If that is what you want, now you can add the procedure to your dB.

    '************************************************* ****
    DO THIS ON A COPY OF YOUR DB UNTIL YOU ARE COMFORTABLE

    Once more: DO THIS ON A COPY OF YOUR DB UNTIL YOU ARE COMFORTABLE
    '************************************************* ****

    Go to the VBE editor.
    Copy the procedure from the example dB, then paste it into a MODULE in your dB.

    In the procedure, you will see 3 lines:
    Code:
        Const sTableName As String = "[tblProjProd]"    'your table name
        Const sDateFieldName As String = "[ProdDate]"   '<<= DO NOT use "DATE" as a field name!!
        Const sVolumeFieldName As String = "[ProdVol]"  'volume field name
    Change the table name to YOUR table name. (tblProjProd)
    Change the date field name to YOUR date field name. (ProdDate)
    Change the volume field name to YOUR volume field name. (ProdVol)

    So if your table name is "ProjectedMonthlyProduction", the line should be:
    Code:
        Const sTableName As String = "[ProjectedMonthlyProduction]"    'your table name
    After the changes, click anywhere in the procedure, then press the "F5" key.
    Check the table for 366 records.

    Attached Files Attached Files

  5. #5
    NoviceAccess is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Location
    Houston
    Posts
    6
    Hi Steve,
    Thanks a ton for your time and effort. My problem is slightly different. I have a table with all the months and its corresponding production volumes, e.g.

    Month Projected Volume
    January 100
    February 200
    March 300
    ...... so on until December.

    I want to automatically create all the days of the month and assign the projected volume of that particular month to all the days of that month. Days in January will have projected volumes 100 from 1st January to 31st January. Likewise February will have projected volumes 200 from 1st February to 29th February. The output table will look like:
    Date Projected Volume
    1/1/2016 100
    1/2/2016 100
    1/3/2016 100
    ...........
    2/1/2016 200
    2/2/2016 200
    2/3/2016 200
    ...........
    ...... so on until December.

    Looks like it is not a big deal for you guys. Hope you will have some more time to help me out.

    Regards

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I want to automatically create all the days of the month and assign the projected volume of that particular month to all the days of that month. Days in January will have projected volumes 100 from 1st January to 31st January. Likewise February will have projected volumes 200 from 1st February to 29th February. The output table will look like
    Looks like I misunderstood..

    OK... you are going to have to be more specific:
    What are the table names?
    What are the field names? Remember that "DATE" is a reserved word in Access and shouldn't be use as a field name. Plus it is a built in function.
    Are the daily values (Jan days 1 - 31) in the same table as the monthly values?


    Or you could post your dB.....

  7. #7
    NoviceAccess is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Location
    Houston
    Posts
    6
    Hi Steve,
    Thanks again. I am uploading a simple database with only two tables.
    What I wish is that the table named "MonthlyProj" is read by the code and creates/modifies a new table exactly like the table named "DailyProj". Essentially this table is the output table and I have put it in the database for your reference only it can also be a query if you like. i have used "Day" instead of "DATE" as it is a reserved word.

    Sorry for the confusion but the rates in the table "MonthlyProj" are the production rates for each day of a month. When you open both the tables it will be clear what I am trying to do.

    Let me know if I have not been able to clear the confusion yet.

    Regards

  8. #8
    NoviceAccess is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Location
    Houston
    Posts
    6
    The database as an attachment.
    Attached Files Attached Files

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this???

    I changed MonthlyProj PK field to Long integer instead of Autonumber. You shouldn't be adding any more months....I hope

    I changed fields "Month" to "ProdMonth" and "Day" to "DailyProd".
    "Month" and "Day are reserved words.
    Attached Files Attached Files

  10. #10
    NoviceAccess is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Location
    Houston
    Posts
    6
    Hi Steve,

    That was awesome !!! Perfect.
    Again, I really appreciate your time and effort.

    Best Wishes
    NoviceAccess

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help...

    Good luck with your project.

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

Similar Threads

  1. Replies: 8
    Last Post: 03-15-2016, 02:35 PM
  2. Replies: 5
    Last Post: 09-29-2015, 12:40 PM
  3. Replies: 3
    Last Post: 09-22-2014, 04:38 PM
  4. Display Age in Years, Months, Days
    By jsimard in forum Programming
    Replies: 1
    Last Post: 01-18-2012, 08:08 PM
  5. Replies: 1
    Last Post: 11-12-2010, 01:16 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