Hi
Currently I have a form with these variables
- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time
In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM
or 3 shifts asset will start at 7:00AM finish at 6:59AM
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.
What I have already done is allocate shifts per asset e.g.
when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM
also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
This is the scenario
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible? If so how do I need to write a VBA or is there a much simpler way?
9 1340
I don't know your table structure but if you have the date time that it went offline, the date time that it came back online, and the start and end date time of the shifts, then what you can do is join the shifts to the offline online data and return all those where the date time ranges overlap. Then you can use the DateDiff() function to calculate the number of minutes between the two date time ranges.
This is a snip it of my table, unfortunately I couldn't paste a print screen image so I typed it out.
Tbl_Department_Asset
Asset / Effected Area / Shift / Start / Finish
Sand Mixer/ Foundry/ 2 / 7:00AM /11:00PM
Heat Treat Oven / Stainless/ 2 / 7:00AM / 11:00PM
Centre Lathe / Machine Shop/ 3 / 7:00AM / 6:59AM
What the table structures you have posted, you should be able to implement what I laid out in post #2.
Can this all be done in 1 box? or do I require multiple boxes for the 2-3 shift variable?
I don't know what you mean by "1 box". Box for what?
Your shifts are in separate rows in a table, not in boxes. The algorithm I laid out in post #2 is how to create a query to return the downtime numbers you described in the first post.
I need the calculation to be in my main "database table" and not just in the query form. Therefore I need a function that will do these calculation in the table itself.
It's usually a bad idea to store calculations in the table itself. It's better to have it in a query so that the calculation will always be up to date. When you store it in the table itself, you have to worry about it being out of sync.
I understand that, and that's what I explained to my superiors regarding up to date, "data" however they want it as a 1 point information with all present and historical information, thus why they want the full table.
In that case, you still do what I laid out in post #2, except change it from select to an update query.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Scott Castillo |
last post by:
Does anybody know how to auto-submit a form in PHP without Javascript?
For instance, submit form variables to self, validate the data in PHP on
same page, then, if no errors, auto-submit the data...
|
by: G-Factor |
last post by:
Hi all
Been learning C++ for about 5 weeks. I find that I create quite a lot of
variables. Generally, is it better to create a new variable for every
different thing that needs to be stored in...
|
by: DaVinci |
last post by:
I did a google web & group search and didn't find an answer to my
question so... here we go. Will try to keep it short. (I am not a
complete beginner but am still pretty low on the food chain).
...
|
by: wandoledzep |
last post by:
I'm using Visual Studio .NET 2002
I remember there was a way to do this in C, so there should be a way of
doing it in .NET:
Instead of:
Dim bolSquare1 as Boolean
Dim bolSquare2 as Boolean...
|
by: reshmidoudou |
last post by:
a string contains an equation and I have to break the string into integers and other variables :
example of the string is "2 + 3 = 5"
I have tried to use substr but it does not work as I can...
|
by: MajorSetback |
last post by:
I am using the Redhat version of Linux and GNU C++.
It is not clear to me whether this is a Linux issue or a C++ issue. I
do not have this problem running the same program on Windows but...
|
by: DaveC |
last post by:
I always used to initialise variables at declaration, then a couple of
colleagues started telling me it was bad practice and that the compiler
should be left to spot the use of uninitilised...
|
by: piscesabhi |
last post by:
Is there a way to auto resize the website, when using different browsers?
I was using this javascript earlier but i don't want to specify dimensions........i would like it to automatically adjust...
|
by: JOYCE |
last post by:
Look the subject,that's my problem!
I hope someone can help me, thanks
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |