Results 1 to 11 of 11
  1. #1
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8

    Forecasting Consistent Hours

    Hi all,

    I wasn't sure which sub-forum to put this question in, so sorry if this is the wrong one.

    I am trying to create a project management database, and want to assign forecasted hours to each specific set of days. For example, my base table is set up like this:

    Task Start Finish Hours per Day
    Make a Database 1/1/2019 1/4/2019 1
    Browse Access Forums 1/2/2019 1/3/2019 3
    Task 3 1/3/2019 1/5/2019 2

    And I think the final table should look something like this:

    Day Total Hours
    1/1/2019 1
    1/2/2019 4
    1/3/2019 6
    1/4/2019 3
    1/5/2019 2


    Where "Total Hours" is a sum of all the hours for tasks assigned on that day.


    I'm stumped on how to proceed from the first table, which is why I'm asking for help. I think there should be a table with just a long list of dates and an empty column titled [total hours]. Then there should be some form of a query that runs through each task in the first table, and for each day in the second table within the range of Start and Finish, it adds its hours to the second column in that second table. I have no idea how to accomplish this however, and am unsure if that is the ideal solution. Does anyone have any advice for how to proceed?

    Thanks so much in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The only way that comes to mind is "brute force". Maintaining table 2 would be problematic, as any change to table 1 makes table 2 garbage. I'd probably have a process for reporting that got the min/max dates from table 1 and populated a temp table with all dates in between. Open a recordset on table 1. Within a loop of that recordset, I'd have a loop using the start/finish dates, within that code to update each day's record with the table 1 value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not following.
    Why is 1/1 not = 4 and 1/3 = 6? If 1 hour per day was spent on a task between 1/1 and 1/4 inclusive (4 days) then it ought to be a total of 4?
    Also, 1 hour spent on task 1 on 1/3 + 3 hours on task 2 on 1/3 + 2 hours spent on task 3 = 6. You didn't show a grouping on task, but by date, so I get different totals.
    If this is related to employment situations, have you considered that a date range might go over days where work isn't done, such as on days off? That's another can of worms.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This help Micron?

    1/1/2019 1/2/2019 1/3/2019 1/4/2019 1/5/2019
    task 1 1 1 1 1
    task 2 3 3
    task 3 2 2 2
    Total 1 4 6 3 2
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Each data in the second table is a sum of all of the work to be completed on that date. 1/1 = 1 because the only "task" to be completed on that day was "make a database" which was only scheduled for 1 hour each day. So it was scheduled for 1 hour on 1/1.

    It is related to employment, and to make it even worse, every other friday is off. I have a table that lists every working day for the next 10 years, so that is what I have been using to make this database so far.



    RE: Pbaldy

    Thanks for the help. My goal for this was to essentially just spit out that table 2 report and have table 1 be a linked excel. So the base database would be saved, and whenever we needed to see the new forecast we could run the saved database and capture the new info, but not save the database. Essentially it would be used just as a method to do the calculations.

    With regards to looping through the recordset, would that be with ADO? Where would I do that/ "open" the recordset within access? Sorry for all the questions I don't have much experience with that part of access. Thank you so much for your help though.


    Another solution I thought of would be exporting that first table to a CSV or Json and writing a Python script to do that brute force method. Is there any disadvantage to doing that vs doing it in access, besides the import/export process? My reasoning is I have very limited ADO/VBA experience and am a little more comfortable with Python.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by pbaldy View Post
    This help Micron?
    Sigghhh... I guess I shouldn't ask why not start with a cross tab query and base a Totals query on that? After all, we all know? calculations usually shouldn't be stored so I'd ignore the original use of the word "table". But I hesitate at this point...

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would use DAO for the recordset, but that's because I'm more comfortable with it vs ADO. If you're more comfortable doing the work in Python, I don't see a reason not to go that way. At the end of the day, it's your baby and perhaps it's best staying with what you know. I jumped the gun and roughed it out before Micron asked the smart question about days off.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by jrfost View Post
    I think there should be a table with just a long list of dates ...
    An useful idea. But unless this is your only project (and only project in your form too), you create a special calendary database instead (the best will be an SQL database, but one made in access will work too). And you don't need any front-end for this calendary database.

    The calendary database contains a single table, e.g. tblCalendary. The obligatory column will be a PK column which contains every date from start one to some fixed date in future. You can add columns with weekday numbers/characters, week numbers, holiday codes, number of regular working hours, etc. - whatever you may need in this or in some other project, and what is not specific for single project (i.e. working hours for specific employee must not be in this table.).

    The access to this database is free (when you design it as I lined it out, there will not be any restricted information. Every project can have tblCalendary linked.

    In your project, you'll have your base table. Instead of 2nd table, you design a saved query/view, which reads for every task in tblTasks dates from linked calendar table (you can use filters to leave out weekends and holydays there).

    Ant then you need another query(ies)/view(s), which read(s) from 1st one info filtered e.g. by time period, and summarizer it over tasks, days, or tasks and days

  9. #9
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Quote Originally Posted by ArviLaanemets View Post

    In your project, you'll have your base table. Instead of 2nd table, you design a saved query/view, which reads for every task in tblTasks dates from linked calendar table (you can use filters to leave out weekends and holydays there).

    Ant then you need another query(ies)/view(s), which read(s) from 1st one info filtered e.g. by time period, and summarizer it over tasks, days, or tasks and days
    Based on that, how would I expand it from the 2 listed dates (Start and End) to every date in between? I'm not too proficient at access so I'm not sure I follow what you are suggesting. Thank you for your response though, I appreciate it.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    ...how would I expand it from the 2 listed dates (Start and End) to every date in between?
    An example is in attached DB
    (For example, I created a calendary table directly in DB. No way to use linked table in uploaded and then downloaded DB And I used European weekday numbering in calendary - you can edit update queries and recalculate additional fields in calendary table when you prefer other week definition. Of-course you then have to edit other queries too.)
    Attached Files Attached Files

  11. #11
    jrfost is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Sorry for the late reply.

    Wow thank you so much!! This is very very helpful. I am going to try and apply it to my table. I really appreciate it


    ***Update

    I got it to work! Thank you! This was exactly what I needed

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

Similar Threads

  1. Access not consistent
    By johnseito in forum Access
    Replies: 3
    Last Post: 08-11-2018, 05:51 AM
  2. Replies: 2
    Last Post: 08-27-2014, 12:20 PM
  3. Replies: 1
    Last Post: 08-08-2012, 01:42 PM
  4. Access to Excel: Financial Forecasting Tool
    By BLUE_CHIP in forum Import/Export Data
    Replies: 2
    Last Post: 06-19-2010, 01:52 PM
  5. Query Bug? Not returning consistent results
    By trb5016 in forum Queries
    Replies: 4
    Last Post: 06-15-2010, 12:00 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