Inserting records and re-sorting

[jacc14]

I have created a subform that you enter details of a job to be run on a machine. By entering a date and time it calculates the expected finish time using the amount the machine can produce in an hour and the quantity required. This works well.

However I have been told that sometimes the user may wish to slot something in the day which would mean once they had typed the details in, they then have to manually adjust all the start times accordingly for the day which is tiresome.

I was wondering if there was a button I could put on which would look through the records and resort all the start times.

To add to the problem if it goes over midnight then the start date would need to alter too.

I have attached an excel spreadsheet to explain what I mean and also the database I am working with.

Best regards

Christine

[nico5038]

Hi Christine,

I see you're running into the known "deductable data" problem.
In a normalized database we would only record the StartTime (or Endtime) and the ElapseTime (or the needed quantity when that's defining the needed time).

In your initial design the StartTime and the needed Quantity are all needed to calculate the Endtime. You would extract the Qty/Hour of the selected machine (I guess different machines can have different Qty/Hour production). Now a division of the required Qty by the machine production per hour will give the job duration.
Adding this duration to the StartTime will give the Endtime.

On the other hand the user might want to specify the Endtime and you can calculate the Starttime using the Endtime -/- the calculated duration.

Getting the idea ?

Nic;o)

[jacc14]

Hi Nic. Thanks for the response and I see what you are getting at and it works as you have detailed. However the user would need to put in the start time which would automatically do the calculations. I am trying to find a way of the start times changing if I was to slot another job in.
Thanks for taking the time to respond
Kind regards
Christine.

[nico5038]

Guess you want to plan the jobs with the machines.

Still using the starttime and (calculated) duration will work, however you'll need an additional check for the availability of the machine.

This might be daunting, as you'll need to take the "up-time" of the machine, time to switch from one production job to another and availability of the operators. All this can be used to assign a job into a free time slot. So a kind of reservation system.

When you "drop" the startime in first instance, but have the jobs prioritized so there's a way to establish a know sequence, then you can assign the starttime based on the machines availability. This will however need some "heavy" VBA coding, especially when the described extra's (switch time, operator availability, etc.) are being taken in account.

Getting the idea ?

Nic;o)

[jacc14]

@nico5038
Thank you Nic. Food for thought there. Looks like i will be getting the vba book out again but you have definitely given me something to think about.

best regards
Christine

[nico5038]

Designing a database isn't easy. You'll always have to take all user requirements in account and ask them a lot of questions. In general I use the datamodel to check or all needed processes can be supported and when all their processes (tasks) are clear I start programming.

Glad you saw the need for thinking out the design as that's exactly what the intention with of previous post was. Don't hesitate to post more requirements and or design questions, as the best designs I've made were always based on discussions with my colleague designers. No one can foresee all situations and that's the big benefit from sites like this.

Success and keep me posted :-)

Nic;o)