We help IT Professionals succeed at work.

Using Access to project (13 weeks ahead), completed jobs by remaining hours

carlosm06
carlosm06 asked
on
602 Views
Last Modified: 2016-08-29
This is real difficult, well maybe for me, but I am trying to create an Access program that will project hours remaining for a job assigned. The fields that I need to be displayed are Job, Task, Contractor, Description (these fields are pulled from a table), % complete (which is entered manually), and hours remaining (calculated field-query) and the days of the week (M, T, W, TH, F, S, S) thru 13 weeks.

The way the hours remaining works is
a. Jobs greater than 160 hours each has to be broken down by 32 hour days
b. Jobs less than 160 hours, but greater than 80 hours are broken down by s4 hours
c. Jobs less than 80 hours are broken down by 16 hour days

An examble of how it would look is below:
Job        Task       Contractor     Description     % hrs comp   Hrs rem   M   T   W   TH  F   S   S    M--> 13 wks
8560      08            Burks            Framinn               0%               12        12
8912      01            Lieman          Deck work          85%             161      32-32- 32- 32-25             32...
8815      04            Hilton             Siding                 0%               120      24-24- 24- 24-24
8650      03            Boyden         Columns              0%                78      16-16- 16- 16-14
8112      05           Atlantic          Framing               0%               740     11-23- 23- 32-25              32....

Totals                                                                                                    95-95- 95- 95-95

The totals for each day can be no greater than 95 total hrs which is why the numbers are split like that. There are more rules to this, but if I can get this part done first I will give out more points for the additional rules. I probably figured a crosstab would work here, but I could be wrong. Thanks
Comment
Watch Question

Commented:
The 161 hour sum does not add up.  I get 32-32-32-32-32-01

Commented:
Plus with that one hour worked on Sat, it should be finished, not carried over into the next week

Author

Commented:
That's the thing and part of another rule, but I can't have a left over hour for another day so I have to lump it together. It has to add up to 95 total hours at the bottom. If there were 2 hours left over, I could extend it to the following day and incorporate that into the next days totals...

Author

Commented:
Actually the table is a bit distorted, but the 161 should be 32hrs Mon thru Thursday and 25 for Friday. If I put 32 for Friday as well then the total for that day at the bottom would be more that 95 so I assigned 25 on Friday (which totals 95) then 8hrs to be pushed to Monday and that will equal 161.
Donald MaloneyConsultant

Commented:
you have 32-32- 32- 32-25             32...  = 185
which goes into the second week.

and for the hours like 120    24 24 24 24 24       it does not go into the second week.

and  740     11-23- 23- 32-25              32....
would go 24 days (4 weeks of 5 days per week then a week with 4 working days)  with the last day 30 hours.
Is that right?
Do you want this in a report?
will you need the data to know who worked what hours on what job for which contractor?
How do you get hours remaining?
Also if the 740 hour job was first would the first hours be 32 and the last JOb 11?
And what if ther was another 120 hour job?
would the 740 hour job be 0 until one of the others ran out of hours?

Actually I'm asking for your logic if there are a lot of jobs and how the code would break it up?

Don
Don

Author

Commented:
Ok, I should of put 8 not 32 so it would add up to 161. 8 would go into the second week. The first 5 jobs on the list  would somehow total 95 for that Monday thru Friday. I guess the way to think about it would like a spreadsheet, theres a list of jobs by days of the week. Once the first 5 or 6 jobs total 95 hrs per day and eventually are completed then the following jobs will start in the second week with each day totaling 95 hrs for those jobs.

Would a report be better that a crosstab? A user would have to enter a start date and from that date the report or so would extend 13 weeks to view.

The data is to forecast how many hours are being worked by our employees on these jobs. If Hours remaining are greater that 160 then we can schedule 32 hour days, greater than 80 and less than 160 we can schedule 24 hour days, less than 80 we schedule 16 hours per day. If there are jobs that like 8 hour remaining, we can find a way to fit that in if we need something to total 95hr for that specific day or when it's convenient.

Let me know if I answered all ur questions, a report would be fine.
Donald MaloneyConsultant

Commented:
I can see doing it in a dimensioned array in code.  I think a xtab query would have too many variables regarding number of jobs and then breaking down the hours and making sure total = 95.
with 13 weeks it would run off th epaper.
it seems a table/report with :
Week 1 Date mm/dd/yyy
Job        Task       Contractor     Description     % hrs comp   Hrs rem   M   T   W   TH  F   S   S    M--> 13 wks
8560      08            Burks            Framinn               0%               12        12
8912      01            Lieman          Deck work          85%             161      32-32- 32- 32-25             32...
8815      04            Hilton             Siding                 0%               120      24-24- 24- 24-24
8650      03            Boyden         Columns              0%                78      16-16- 16- 16-14
8112      05           Atlantic          Framing               0%               740     11-23- 23- 32-25              32....

Totals                                                                                                    95-95- 95- 95-95
Week 2  Date mm/dd/yyy
Job        Task       Contractor     Description     % hrs comp   Hrs rem   M   T   W   TH  F   S   S    M--> 13 wks
8560      08            Burks            Framinn               100%               0      
8912      01            Lieman          Deck work          85%                32    32
8815      04            Hilton             Siding                 100%               0      
8650      03            Boyden         Columns              100%               0      
8112      05           Atlantic          Framing               16%               626    32-32-32-32-32
Totals                                                                                                    64-32-32-32-32

Is this more in line  with what you want?

week number and job number could be indexes but what ties the job numbers to a single set of tasks or is there only 5?

Now could there be several jobs that are over 160 hours  and how would the application know to not use all 32 hours?  What is the logic to reduce hours?  Can that happen?  More jobs with 160 hours?
Don

Author

Commented:
Ok lets say I have 25 jobs all listed vertically. The first 5 are assigned a breakdown of hours based on the rules (under 80 gets 16, over 160 gets 32 etc.) Once the top jobs are completed in hours, then we would move on to the next group on jobs and designate the hours for either the same week or the following week and so on.

The weekdays will list horizontally for 13 weeks at a time including  saturday and sunday (Maybe a job could run thru sat). I have calculated field that determine - Hrs Remaing -  which is linked to the Job.
I added a paremeters to determine what day the 13 week interval would start from. So if the user entered May 15 then the interval should be from May 15 thru Aug 14 (13 weeks), I don't know how to do that as well.

They're several jobs over 160 hrs so each day that hours are scheduled, it should be also reduced from - Hrs remaining - field. If we start with 160 and schedule 32 hrs for Monday, in memory we would only have 128 hrs and so forth. Jobs can range from 2 hours to 900+hrs so based on the total for the week it should be able to extend alotted hours thru a 13 week interval.
That 0% line just means that we haven't started the job yet.

Commented:
You can get each of the days from May 15 to Aug 14 by using a small table named Nos, with an integer field No containing the consecutive values from 0 to 90

For example, in your Jobs table, do a cartesian join on Nos:

SELECT Jobs.*,DateAdd("d",Nos.No,Date()) AS WorkDay From Jobs,Nos ORDER BY Job, DateAdd("d",Nos.No,Date());

Will return a record set with 91 records per Job starting from today.

Commented:
Will return a record set with 91 consecutive WorkDay's  per Job starting from today.

Author

Commented:
How would I grab the date from the parameter to determine what day to start with?

Commented:
SELECT Jobs.*,DateAdd("d",Nos.No,[EnterStartDate]) AS WorkDay From Jobs,Nos ORDER BY Job, DateAdd("d",Nos.No,[EnterStartDate]);

Author

Commented:
This stretches out the result vertically by day. How would I get the days to be displayed horizontally
m  t  w  th  f  s  s  m  t  w  th  f  s  s  m  t  w  th  f  s  s  m  t  w  th  f  s  s  etc....
Donald MaloneyConsultant

Commented:
Re start and stop of hours:
in the first example
Job        Task       Contractor     Description     % hrs comp   Hrs rem   M   T   W   TH  F   S   S    etc
8560      08            Burks            Framinn               0%               12        12
the first job is completed on monday andt ther are four jobs continuing on until their hours are done.
If there was a sixth job could it start on Tuesday and then job 5  (8112   05   Atlantic   Framing               0%    740   11-23- 23- 32-25)  would have its hours changed OR do the jobs then run 2 - 6    OR  do  jobs 6 7 8 start on the following Monday since only jobs  2 and 5 (8912  8112) have hours that extend to the second week?

In GRayL's query (nice query GRayL) the jobs run for all 91 days and there would not be hours in all of them.
Im asking when do jobs 6 through 25 start?
 Am thinking of a form where you would select a date and the form would show the jobs and hours for the week that includes that date.
Also, does any of the data need to be saved for history.  e.g.  you enter the jobs for the week of June 11.  The application does its thing and you get weekly job projections.
The week of june 25 some jobs are done - do they get deleted?
The week of July 25 new jobs are added Do you have a start date field so the app knows when to start including them in the 13 week progression?
Do you ever work Sat or Sun?   WHat about holidays?
DOn


Commented:
Use a crossTab query.  However, you are going to run into a problem with you day naming convention.  
The best I can come up with is 00We sequentially to 90Tu, where the integer is the number of days since the [StartDate].  Is That acceptable?

Author

Commented:
Yes, the jobs will start right away so the sixth or even seventh job can start on Tuesday (as long as the Total for Tuesday at the bottom doesn't exceed 95 total hrs for that day. Whatever jobs are not completed within the week are extended thru to the second, third or even fourth week (depending on how big the job or how many hrs remaining).

I wanted to confirm GRayL's query, I don't want each job to run for 91 days just have each day shown horizontally for 91 days. There will be plenty of jobs listed that won't start until the 4th week, etc. so I would like to read accross that line to see when that jobs starts and on what day. No data has to be saved really. If the job is completed then it should be removed. I need to have a prompt so the app would know when to start the 13 week progression. There are some days that will require work on Sat or Sun, as well as Holidays but I was thinking of having that in a prompt (parameter). I think you are on the right track... Thanks...

Author

Commented:
I was thinking cross tab initially, but I am not good creating crosstabs of this magnitude..

Commented:
This will give you all the 91 dates (00nn) format for each record in the Jobs file.  Play with this and try adding your conditions.  I'm not certain I want to go there. Remember, the TRANSFORM is going to change as you add your conditions.

TRANSFORM First(HrsRem)
SELECT Job, Task, Contractor, Description, [% hrs comp], DateAdd("d",No,[EnterStartDate])
FROM Jobs, Nums
Group by Job, Task, Contractor, Description, [% hrs comp9 DateAdd("d",No,[EnterStartDate])
Pivot Format( Format(DateAdd("d",No,[EnterStartDate],"y")-Format(EnterStartDate,"y"),"00") & Left(Format(DateAdd("d",No,[EnterStartDate],"ddd") ,2)

Commented:
What you have yet to introduce is the JobStartDate.  Put that into the SELECT and GROUP BY clauses and use a WHERE clause after the FROM to show where the Data will start in relation to [EnterStartDate]

WHERE JobStartDate<=DateAdd("d",No,[EnterStartDate])

Author

Commented:
I only need one start date which in this case could be [EnterStartDate]. The job start date is already populated in a table so we don't have to worry about that...

Commented:
Just never saw it before.  Anyway, there is the basic CrossTab on which you have to build your exception rules.  I've just done a quick review of what has been written to date and...  I think I've got a headache!

Author

Commented:
I know it's pretty intensive, the enterstartdate was only going to serve from where the user wanted the the 13 week progression to start.

Commented:
I understand.  With all the jobs having a JobStartDate, the query allows the user to [EnterStartDate] from which the next 13 weeks will appear.  If the JobStartDate is later than any of the [EnterStartDate]+Num (looking at this No should be changed to Num) then there will be no data from the TRANSFORM clause provide the WHERE clause is there.  Make any sense?

Author

Commented:
donaldmaloney: i thought you were on to something. You were thinking of a form I believe...

Author

Commented:
I can't get the PIVOT clause to work here, The pivot will allow me to view the days vertically right?
Donald MaloneyConsultant

Commented:
Thinking on the pc here.
SO you start with 5 jobs adding to 95 hrs.
As they complete then job 6 would fill in then job 7  etc to fill the day so that it always adds to 95 hrs.
Correct?
If so i'm thinking that GRayl's query will populate a table with all the dates.
Then when job 6 starts its information fills in and replaces the job that ended and on and on with job 7 8 9 10 etc.
Then in a form/subform show Date and the days of that week and then 10 rows with Job  Task   Contractor     Description     and the hrs for  M   T   W   TH  F  S  S  but the last S S are always 0

In your example above the first 5 would show for week 1  and Job 6 would show foe T W T F
So you would see 6 lines of data.
Then in week 2  you would see Jobs 2 5 6 7 8   since jobs 1 3 and 4 are done.
IS this what you were looking for?
Don

Author

Commented:
Yes that's correct jobs 6 and 7 jobs will continue. I was trying to get the query to populate the dates but I could'nt get it to go. Job 6  would just continue with it's hours with job 7, 8, 9 and so on. So yeah, that is what I am looking for. Thanks, I appreciate it.
Donald MaloneyConsultant

Commented:
OK. I don't mind writing some code but
1. do you have any code written yet?  e.g. forms application?
If so can you upload it to www.ee-stuff.com so we can look at it?
2. Is 10 the maximum number of jobs that would be running in a week  e.g 5 finished and 5 started?
This seems like an interestin coding project and im thinking thats whu GRayL said "I'm not certain I want to go there. "
Don

Author

Commented:
I tried to upload the form but the file is too big, they're 4 tables that are there as well where the data is coming from. I had a box that had the prompt for start date (which should the 13 week interval to start). Once that's entered the main form would popluate with those fields above:
Job, Task, Contractor, Description (these fields are pulled from a table), % complete (which is entered manually), and hours remaining then the days of the week in a 13 week time frame (m t w th f s s m t w th f s s....etc
Donald MaloneyConsultant

Commented:
Did u Zip the file?

But first do a compact and restore

tools  > database utilities > compact and restore

That reduces the MDB size.

then Zip it and upload it to www.ee-stuff.com.
Don

Author

Commented:
I was able to upload it without zipping it to www.ee-stuff.com. Thanks,,

Author

Commented:
I uploaded it again. In the forms section, there is a form called Scedule form which is the first screen the user would see. It has parameters, the only one of concern is the "enter start date' right now and when you click ok it's suppose to run the query to bring up that layout that I wanted. I populated everything except for the days of the week thru 13 weeks and the breakdown of hours for each job in the query. Thanks..

Commented:
carlos:  You have to post the URL of the uploaded file back here so we know where to go for it.  Go to the uploaded file location in ee-stuff.com.  Copy the URL from the browser window and paste it in a new post back here.

Commented:
BTW the TRANSFORM clause provides the value in the intersection between the vertical values created by the SELECT clause and the horizontal fields created by the PIVOT clause.

Author

Commented:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22629286.html
That is my URL from my browser. I don't why I had such a hard time with the Tranform and Pivot clause. If you try the transform and pivot clause in the sql view of that query you will the problem I ran into. I know thats how to get the days displayed but it wouldn't work.

Commented:
No, you've given us the URL of this thread.  Go to the upload area where your uploaded file is and get that URL.  BTW I cannot believe you were able to upload an mdb file without zipping.

Author

Commented:
http://www.ee-stuff.com/Expert/Upload/upload.php
That's the url, let me know if you can view the file.

Author

Commented:
I zipped it and sent it this time around.. thanks

Commented:
That is the URL of the entry page to ee-stuff.com.  We need the URL of the page which is displayed after you successfully upload your zipped mdb file.  

Commented:
1. Zip you mdb file
2. go to http://www.ee-stuff.com/Expert/Upload/upload.php 
3. Select Expert Area - File Upload
4. Paste the URL of this question
5. Browse for your mdb.
6. Add a comment - this is compulsory
7. Click Upload
8. If the upload was successful, you will be so notified - paste the URL of that page back here.

Author

Commented:
There are 2 links:
https://filedb.experts-exchange.com/incoming/ee-stuff/3713-copy-of-shop-wip2.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/3715-copy-of-shop-wip3.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/3827-CArlos.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/3719-13weeks.zip
 
and
https://filedb.experts-exchange.com/incoming/ee-stuff/3713-copy-of-shop-wip2.zip 
I didn't realize that if there was no comment you would never successfully upload, now I know...

Commented:
The second URL is the correct one.  I'm running Access 2000 and I get an 'unrecognizeable format' error message.  If you are running A2003, you can save the mdb in an Access 2000 state.  Can you try that, rezip, and upload anew?

Commented:
Now all the linked tables create an error.  Can we recap here?  Both Donald and I think the criteria for breaking down the work remaining for each job is beyond the normal use of Access - it is more becoming a rather complex Program Management tool.  I don't have the time nor the inclination to design such a beast using Access components.  I have given you a way of getting the next 13 weeks dates along side each record in your Jobs table using a CrossTab.  However, expanding that to include the daily hours for each job with all the caveats and criteria you are imposing is beyond me.  Sorry, just my 2ยข.

Author

Commented:
Can you open any of the tables. Is the broken link making the task impossible. Were you able to get the next 13 weeks alongside each record on the mdb file that I uploaded. What do you reccomend if Access isn't robust enough for this project? Thanks for all your help...
Donald MaloneyConsultant

Commented:
I think I have an idea about how to get it done.  Just a little patience since I will have limited time on the pc.
I'm thinking that a dimensioned array say 25 x 91 may hold all the data.  Then its a matter of storing 5 jobs per date.  Then to show it on a form/subform so they will show the jobs this week . all including Date to Date + 6. still working on the display form(s) but i think i have a general vision of at least 1 way to proceed.
Don

Author

Commented:
Thanks that would help tremendously. if you want show me what you have so far that would be fine as well since you have the 13 weeks displayed already. I kinda figured populating the hours was the hard part.

Carlos

Commented:
I can only open Nos.  I can see the structure of the tables in design view.  Those are busy tables.

The problem area here is one of tying the rate of work against hours remaining, and limiting the resulting sum to 95 hours per day, giving your self options as to whether you work Sat or Sun.  In a Scheduling system its like using limited resources to to a large job.  Earlier I said Program Management, but the more I think about it, the more you require a good Scheduling system that can modify the rate of work according to the work remaining.  It's a different twist, but there's bound to be packake out there somewhere that can do it.  Try a Google on Project Scheduling Software.
Donald MaloneyConsultant

Commented:
Carlos, I really don't have anything wriotten yet.  Just ideas.  Through the thread here  I've been getting a good idea about the specs.  Don't see much of an issue with having the hours per day work out but am wondering, and you can help me here, if ther are 5 jobs and they all are over 160 hours (32 hours per day do you just do three 32 32 31?  Or do I apply a "proportional amount" to each one so I get 19 19 19 19 19 ?
Don

Author

Commented:
I am going to scan in a jpg that show how it looks on paper. The jobs are not displayed with consecutive jobs over 160hrs. It could be 2 in a row You can start job 1, 4, 5, 6, and 10 as long as it adds up to 95 for that day. As those jobs are finishing start 2, 3, 7, 8 and 9 in part of the first week and into the second week depending on how many hrs are remaining. You will get jobs with only 10 hrs or less remaining...

Author

Commented:
I couldn't get the paper to scan because it's so dull so when I increase the dpi it becomes too large to upload, sorry.

Carlos
Donald MaloneyConsultant

Commented:
Carlos,
Was the paper report done in word?  if so zip the document and upload it.
Else upload the scanned inage.  Maybe I can give it a go to read.
Don

Author

Commented:
I put it in an excel spreadsheet. I have to turn it in 2morrow so anything that can be done I appreciate even if its just showing the dates thru 13 weeks. let me know what you think.
Thanks,
Carlos

https://filedb.experts-exchange.com/incoming/ee-stuff/3719-13weeks.zip 
Donald MaloneyConsultant

Commented:
Carlos,
Got it.  Now I am a little confused since, in the sheet you sent the data is set up for week 1 as
Job no Task Contractor Description Shop % Complete Hrs Remaining               M   T   W   TH   F
8563    08    Legg Mason Bend Plates               0             12                              12            
8320    01    Columbia       Framing                     85           109                            32  32  32 13      
8499    01     kids              Framing                      0            120                            24  24  24  24  24
8552    01     Hilton Sales   Elevator Support       0             78                             16  16  16  16  14
8525    02    Shop Rite       Slabs                        90            5
8539    03    Bridger           Framing                      0            6
8407     05    Bridger          Framing                      0            8
8907     07   BlackStone      Stair Framing            0             4
8765    06    BlackStone      Framing                     0           48
8978    25    Shops           High Roof Add             0          740                              11  23  23  32  32
8603     01    95 Wall         Slab Opening               0          488                                                10 25
8731     02    JP Morgan       Framing                     0         416
8536     09    Harv                  Framing      

Job 5 6 7 8 and 9   hrs remaining  5 6 8 4 48 have no hours for monday.
Even though job 9 with 48 hours could hav 11 hours on monday thus adding up to 95.
Or Job 5 could be in monday with its 5 hours and the total would be 89.

What is the logic for using 11 hours from 8978  and NOT 11 hours from 8765?
I am looking for the "rules" to apply to code so it follows the same pattern and will have the same results that you have in your spreadsheet.
Don

                                    

Author

Commented:
The logic is that at the end of each day the totals are 95hours for each day. I could of used 11 hours out 8765 as well. You generally start the bigger jobs  and then fill in the smaller ones at the days go on.... Job 5 couldn't start cause it wouldn't total 95. It sounds like you have it though...

Author

Commented:
Was my last statement helpful?
Donald MaloneyConsultant

Commented:
I think so. The logic is that on any given day the hours add up to 95 and there are no more than 5 jobs active for the day.
Is that correct?
Don
Donald MaloneyConsultant

Commented:
And once a job starts it should continue each day until completed?

Author

Commented:
That's absolutely correct, you got it.
Donald MaloneyConsultant

Commented:
OK.  give me a couple of days to work on it.

Author

Commented:
Could show me how to get 13 weeks in days to display in the meantime?

Commented:
carlos: in my post at http:#a19278044 replace the Jobs table with your table name containing the fields you used in your example.  It will generate a recordset with the 91 days from whatever you enter as [EnterStartDate].  Remember Nums must have values 0-90.

Commented:
That field has to contain the field HrsRem.  If your field is Hrs Rem (with a space) make it so in the query and wrap it in brackets.

Commented:
Carlos:  Able to get it to work?

Author

Commented:
I am going to send you what I have, hold on..

Author

Commented:
I think the problem is doing the cartesian join to get it to work. How do I apply the cartesian join?

Commented:
What are the tablename and fieldnames of the table for which you want the 91 dates?

Author

Commented:
Here is the sql with the error in the Transform clause (at least that's what I am getting)..

TRANSFORM First([Group Task].RemainingHours)
SELECT [Group Task].Job, [Group Task].Task, [Group Task].MASTER_JCM_JOB_1.Description, [Group Task].Shop, DateAdd("d",Num,[EnterStartDate])
FROM [Group Task], Nos
PIVOT Format(DateAdd("d",No,[EnterStartDate],"y")-Format(EnterStartDate,"y"),"00") & Left(Format(DateAdd("d",No,[EnterStartDate],"ddd") ,2);

Commented:
[Group Task].MASTER_JCM_JOB_1.Description
----------------^------------------------^-- cannot have two periods in a fully qualified field

Commented:
In addition you have no GROUP BY clause.

Commented:
Try this.  I removed the troublesome field for now and added the GROUP BY

TRANSFORM First([Group Task].RemainingHours)
SELECT [Group Task].Job, [Group Task].Task, [Group Task].Shop, DateAdd("d",Num,[EnterStartDate])
FROM [Group Task], Nos
GROUP BY [Group Task].Job, [Group Task].Task, [Group Task].Shop, DateAdd("d",Num,[EnterStartDate])
PIVOT Format(DateAdd("d",No,[EnterStartDate],"y")-Format(EnterStartDate,"y"),"00") & Left(Format(DateAdd("d",No,[EnterStartDate],"ddd") ,2);

Author

Commented:
The query name is Group task, I created the table Nos like you pointed to earlier. The fieldnames are [Group Task].job, [Group Task].task, [Group Task].MASTER_JCM_job_1.Description, [Group Task].Shop
Those are the fieldnames, for now at least.

Author

Commented:
ok, let me try that...

Author

Commented:
I got a syntax error in the pivot clause:
PIVOT Format(DateAdd("d",Num,[EnterStartDate],"y")-Format(EnterStartDate,"y"),"00") & Left(Format(DateAdd("d",Num,[EnterStartDate],"ddd") ,2)

Commented:
Sorry:

PIVOT Format(Format(DateAdd("d",Num,[EnterStartDate],"y")-Format(EnterStartDate,"y"),"00") & Left(Format(DateAdd("d",Num,[EnterStartDate],"ddd") ,2)
Donald MaloneyConsultant

Commented:
Hmm, Im wondering if iI am wasting time here.  I was working on th elogice to make the decisions about the hours to be worked for each job for each day.  Total max for each day is 95 and total max jobs per day is 5. Once a job starts it MUST continue day to ady until completed. as Jobs complete New ones will start but only to the limits   95 & 5.  
Also, if there are not enough jobs so that all job hours are less than 95 then they will be completed for the duration of the 13 weeks.
One last Q  if I have 5 jobs running and the hours for them in any particular day are less than 95 :
1. Can this happen?    2. do I keep gouing until 1 or more jobs complete in a day so I can add new jobs the following day?

BUT   I am looking at the thread and it seems as if Carlos wants the transform query so then he can paste the resuklts in an excel sheet and hand calculate the hours per day.

If this is true then let me know so I can move on to another question.

Don

Author

Commented:
No you are not wasting your time, I think GRayL was trying just get dates displayed since I wanted to show this today. I am definitely looking forward to your work. So the query was just to show that I have been working on this and not a finished product.

To answer the question, you will have to keep going until 1 or more jobs complete in a day to add new jobs. I this in a form format so your help is needed Don.

Thanks,
Carlos

Donald MaloneyConsultant

Commented:
OK working on logic based on the rules in the thread.
Interesting project.
Don
Donald MaloneyConsultant

Commented:
I am assuming that the job numbers are assigned consecutively.  So if I sort by Job number and then calculate hours per day the oldest jobs will be assigned hours first.  Is this OK?

Author

Commented:
yes that is ok....
Consultant
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Don. Just getting back from overseas. Project looks good. I need some help on one last thing which is to have a make query to build the jobs table. I have a query that grabs the data you are using and if I can create a make query that run everytime access is opened it could create the table with the field names that you have so the program could run with live data. This is very good.

Thank,s
Donald MaloneyConsultant

Commented:
carlosm06,
Where are you getting the original Jobs Data?   ?> "make query that run everytime access is opened"<?
It looks as if you build the schedule then when the hours start going less than 95 you need to add new jobs.
So at that date you need to keep the jobs selected, add new ones and then rebuild a new 13 week schedule.
The application takes the Jobs table that data and builds the 13 week table from it.
But, I am thinking that you would need a way to add/delete from the jobs table to rebuild the 13 week table.  I would also think that once the table qwas built then you may want the option to keep the results and/or add new jobs starting with a particular week etc.
I  think that this is a new question.  Are you building this app for work?  Does your company have an access programmer?
Don

Author

Commented:
We have a linked data source for this project, but it was too big to upload. I was thinking of having the make query run everytime access was opened (this way way there will always be new jobs). We don't have a access programmer, but this was a project that was odd for us. i don't think we would need programs built like this in the future. I can open the question in a new thread if you think this is best.

Carlos
Donald MaloneyConsultant

Commented:
Carlos,
Yes you can have a macro start when access opens and run a query to build the jobs table.
Do you want to save any of the data or just recreate everything when access opens?
Then Access opens ,  Jobs Table data is cleaned out, an append query runs to add data to the jobs Table, the form is opened to build the 13 week schedule, the schedule goes to Excel for your report.
Is that what you are lookuing for?
Don

Author

Commented:
Yep that sounds like it would be perfect and saving it would be good as well. So this will be exactly what I would be looking for...

Carlos
Donald MaloneyConsultant

Commented:
OK,
I would suggest opening another question.
I assume you already have a link to the file to be ipmorted?
If not we need the name and the file type.
Then we need the structure of the file - field names and data types.
Also put in the uploaded application name:
https://filedb.experts-exchange.com/incoming/ee-stuff/3827-CArlos.zip 

so someone can add the code to it to put in the (Autoexec) macro.
Do you know how to change the app so the autoexec executes on startup?
DOn

Author

Commented:
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.