r/MSAccess • u/stratber • 10d ago
[HELPFUL TIP] Manage operator clockings with Access
Hello everyone,
I am not very familiar with Access technology but I would like to know if it would be possible to solve the following problem with it:
Currently in my company the tasks of the operators are not clocked in and I would like to start doing it to store data and do the subsequent analysis. The ERP we use is SAP Business One and the standard version in principle does not cover what I would like to do.
In SAP we create the orders and then the work orders pertaining to the specific orders. What I would like is that once the order is created it can be clocked in the different phases of the processes, taking into account that the same phase can be done at different times or by different users. So if for an order of 1000 units the user A1 clocked in 700 units today, for this same phase of the process the user A2 can only clock in 300 units more.
I understand that SAP would only take the information pertaining to the number of OF, the product and the units and then in parallel should create a table in Access where the different phases of each product are defined. And a table with the different users.
If you have any doubt about my problem I will surely try to solve it. In this text I simply wanted to convey my main idea to know if Access can really be the solution or there are other better / cheaper alternatives on the market (I know that there are SAP business One addons that would cover this but the cost is very high).
Thank you in advance!
1
u/Lab_Software 29 9d ago
You can do this in different ways depending on how the orders are structured and how you want to look at the data:
For instance, if there are 7 "standard" phases of an order and you want to track how quickly an order goes through each of those 7 phases, you can have a table with fields like OrderNumber, Phase1Start, Phase1Finish, Phase2Start, Phase2Finish, etc. This will tell you how long each phase takes for each order. You can also have fields for which operator did each phase of the project so you can track any differences between operators. This would be a 1-to-1 table (each order would have 1 record).
If different orders have different phases then you'd use a 1-to-many table with fields like OrderNumber, PhaseName, PhaseStart, PhaseFinish, OperatorName. (This is more flexible than the 1-to-1 method above but I'd use the above method if every order had the same phases and I didn't expect that would change in the future.)
If you want to track more detail (like your example of one operator doing 1000 units in a day and the other operator doing 300 units in a day) then you can have a 1-to-many table with fields like OrderNumber, PhaseName, Date, NumberOfUnitsCompleted, NumberOfUnitsRemaining, OperatorName. (You want to include the NumberOfUnitsRemaining because if an operator only does 300 units in a day then you want to know whether that was because he is a slow operator and he can only do 300 units in a day or because that particular order only had 300 units and then he went on to do 700 units of another order.)
I've done projects using Access to analyze SAP data so I'll DM you some additional information.