Build an access macro to create a make-table called Jobs everytime access is opened

Heres the original question
https://www.experts-exchange.com/questions/22629286/Using-Access-to-project-13-weeks-ahead-completed-jobs-by-remaining-hours.html

That shouldn't be too difficult to achieve.
I would create a form and set the database to open this form on start up. (go to the menu Tools->Start up when you have the database open)
Then for this form put some code in the on load event handler. You can either create a macro or code it in VBA. Let us know if you need help on creating this code.

carlosm06:
What is the name of your query that creates the new Jobs list?
Create a delete query to delete the jobs data.
    DELETE Jobs.*  FROM Jobs;

Then an append query to build a new jobs table.
On the original form of the application you cna have a button to run the queries .
Then continue with the other buttons to build the 13 week schedule.

This way the file will not be autonmatically deleted by mistake.
If it was auto onopen then even if you just opened it t o view the dta it would delete the data.
I think it is better to have control with the addition of a button and code behind it to run the delete/append query.
Don

Carlos,
I am thinking something like this on the on_click of the new button.
' *******Start Code *******************
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click


DoCmd.RunSQL "Delete * From Jobs"
DoCmd.OpenQuery "AppendQueryToCombineTablesForJobsAndBuildJobsTable"

' the source Table for the append query would be the query that imports data from the two tables

Me.frmSubformJobs.Requery
    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
   
End Sub
' *******End Code *******************
Don

Ok, the name of the query is called "Sched".  I will create the delete query then an append query to build a new jobs table. Now on the form, creating this button with the code you have provided will do this automatically once click (therefore I should have the form open up when access is opened)?

On my way home home now, I will try this as soon as I get home.

Carlos,
When you build the apend query it will ask "Show Table" with three tabs   click on queries and then click on Sched.
IT will ask for the table that the query will append  select JObs.
Then drag down each field from sched and in append to put the correct field of the jobs table.
Save the query and put the name in and replace the AppendQueryToCombineTablesForJobsAndBuildJobsTable in the code above.

Then when you click the button you will then have a new jobs table.

I would suggest adding a message box that says "Are you sure"  before running the queries.
If No is selected then exit sub.
Don

Don,

Ok, I think I have everything except for one small problem. When the query builds the 13 weeks, its sorting it in order meaning starting from monday (7/9/1007) 90 days will carry you into october (10/9/07). So the forst date that appears in the 13 week schedule is 10/1/2007 thru 10/8/2007 followed by 7/10/2007 straight thru to september. How do I fix that?

Carlos

CArlos,
What is the starting date in the JObs Table?
Look at the data and make sure that they are all the same.

THe form has a button to change all the dates in the jobs table to the entered date.
Also check the NOS TAble the numbers should be 1 to 91.

Can you post a sample db that has the problem so we can check code?
Thenks
Don

I was using 7/9/2007 as the start date. If I use that as the start date, does that date show up on the form or tomorrow's date? I checked the numbers in the Nos table and they are 1 - 91.
I have upload the result of the excel sheet and on another worksheet you will see the data from the "schedule" query resullts.

https://filedb.experts-exchange.com/incoming/ee-stuff/3948-TempJobsExcel.zip 

Thanks,
Carlos

CArlos,
For some reason I cannot open the file in excel  or access.
I have excel and access 2000   are you using 2007?
Can you make it 2000 so I can read it?
Thanks
Don

Sorry about that... I was wondering if the date problem could be due to the short date which leaves off leading 0's when displaying 7/9/2007 rather than 07/09/2007 so 10/1/2007 displays first. Not sure though..

Here you go!
https://filedb.experts-exchange.com/incoming/ee-stuff/3949-TempJobsExcel.zip 

Thanks,
Carlos

Carlos,
It takes quite some time to run this with 362 jobs.
How much time on your pc?
Don

It takes time on mine as well, Is there a way we can take around 30 - 40 jobs at a time only?

Do you have any ideas on a different approach?

I was able to grab the top 40 records from the Schedule query so it take about 35 seconds to build the 95 hours button. So can you eliminate everything after 40 records and then run it. I am having problems with the dates starting in October rather than in July.

Thanks,
Carlos

CArlos,
for your query change "Short Date" to "mm/dd/yy".
The dates will fall out in order.

Am working on new code to speed the calculations.

Q. If I search through the jobs is it OK to stop as soon as I get a combination that adds to 95 hrs or more?
This means that I may be skipping low job numbers until the endo of the 13 week schedule where they will be picked up as the most hours per day that can be combined drops below 95.
Don

Ok..

Absolutely. You can stop if you get a combination of 95 hrs...

Thanks,
Carlos

Carlos,
Worked on it a little and got the 362 jobs to run in less than 4 minutes.

Just a little tricky.  Maybe it will run faster if compiled.

One problem is that I skip all combinations where one of the jobs has 0 hours.
So I may start with only 4 jobs totaling 95 hours when there should be 5.
Or is it OK to have only the first 4 jobs as long as hours = 95
Also  I see that your 362 jobs run only to job 52 in the 13 weeks.
Maybe need to hire more workers???
Link is:
https://filedb.experts-exchange.com/incoming/ee-stuff/3957-CarlosNew.zip 
The Jobs Temp FIle is built already

The button is there to drag in your two queries to build the Jobs Table but you need to add your code to make it work.

Give it a try

Don

Don, This is perfect and you definitely sped up the time to build the 95 jobs. can you stop the prompt "Schedule complete" from popping up for each job though. I am hitting the button for each time.

Thanks,
Carlos

CArlos,
look for  Msgbox  Schedule complete" , vbokonly   at the end of the code and put a ' in front to make it a comment..
Don

Yes, I  ended up doing that write after I sent the post. I think I had a dummy moment when I sent that post, sorry about that..

Thanks