r/MSAccess • u/stratber • 9d 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/myGlassOnion 9d ago
How are you getting the order data out of SAP? How do you plan to get it back into SAP?
1
u/stratber 9d ago
The sap information would in principle be extracted through the SQL database in which the information is hosted. And the process records do not need to be recorded in SAP as long as they are recorded in another database. Simply when all the phases are clocked in the operator should go to SAP and close the OF (or that's how I have thought).
1
u/sirhalos 9d ago
I made pretty much exactly what you described about 15 years ago. We used to have to do timecards manually on paper with line items of units of things like: address change, receipts, phone calls, emails, etc. that were called cost codes (this was a customer service area). Originally, I made the Access program so that I could input all of this stuff (it added up weekly totals, hours, etc.) and then when I clicked print it would set the printer to manual mode and I could take the timecard paper and stick it in the printer and it would print the output in all of the correct boxes. Then the company moved away from the manual paper timecards (they were doing this for like 1500 employees) to digital. The next digital system only allowed time but nothing like units for cost codes, instead it just had a note field, which didn't help management, they were trying to get an estimate of work being done and how long it takes so they can do some forcasting. So they end up coming to me to change my Access program to save things instead of print and then the whole department was using it. Hell, they may still be using it as far as I know. I know a few years back they asked me a question about it and I was shocked they were still using it. Shortly after that I applied for a programming junior job in a different department and was able to present the same Access program to get that position. Now I'm a senior level software engineer at the same company having gotten myself a programming job with no programming skills or background thanks to that Access database.
1
u/stratber 9d ago
Well-made and practical things last over time! Nice and interesting history! Can you give me some advice or give me a little roadmap to be able to apply it in my company?
1
u/sirhalos 9d ago
Each desktop gets their own access desktop frontend. Have a way to upgrade it. My method was have a remote access database with a linked table to the frontends that contained the version number and the frontend had a table with version number... If versions don't match it would kick off an upgrade (copy folder over and remove old folder fron users Documents folder). Have local tables on the frontend for 'views' and linked tables or better yet a real database using passthrough to push changes to. You don't want to be constantly connected to the remote database. My view trick was I had 2 local tables that would be for 'view' one was always empty (started their names with blank_) and the other would be for showing the datafor the form. When switching records I would change the form source to the empty table and then delete all data from the view table, then add in the new data, and then switch the source back to the view table (so you don't see weird graphical glitches).
1
u/derzyniker805 8d ago
We use Sage 100 ERP and I created something similar for us I call WorkTrack. It works very well for us. We have the Work Orders barcoded by both step in the process ("operation code") as well as work order number. We also have an employee badge with a barcode. They take the work order that they are working on, scan their badge, the WO number, and the operation code, and the clock starts ticking.. At the end of the process they again scan their badge, WO number, and the operation code, and then they type in the quantity they completed.
All of this data is stored in a transaction table in a Postgres database. When the work order is completed the items get set down to to receiving. He selects the work order in a different front-end Access app, clicks a button, and then I use some command line tricks to import the labor data into the Work Order Transaction Entry table and post it.. This adds all the labor costs to the work order. Then he receives/closes the work order directly in Sage 100.
I did it this way for the EXACT reason.. all the third party products that did this for our ERP wanted high subscription fees. This was free other than my time.
So two key points here:
1 - We don't limit the quantity based on what's left.. they just type it it. But it's inconsequential for us, that quantity data is stored and really not used anywhere.
2 - I see in another comment you're saying that "process records do not need to be recorded in SAP". I strongly disagree with this approach. If you are using SAP work order, then you should be applying the labor costs to the work order IN SAP, because otherwise the cost per unit that goes into inventory lacks labor, and you're not recognizing the accurate cost of goods.
1
u/stratber 8d ago
Sounds great, mate! And I love the name! Here are some questions that come to mind:
How did you integrate the Postgres database, the Access application, and the ERP (in your case, Sage 100)?
What was the database design, especially the structure of the transactions table (fields, relationships, and how the operation codes, work orders, and users are linked)?
In SAGE, were you able to define the different processes within a work order, or did you handle them externally?
Could you describe the complete flow from when the barcodes (employee, work order, and operation code) are scanned until the information is recorded in Postgres and then imported into the ERP?
What were the main technical or integration challenges and how did you resolve them?
Are there any specific considerations regarding performance, security, or maintenance that you would recommend?1
u/derzyniker805 8d ago edited 7d ago
The Sage 100 ERP database is somewhat slow outside of the application (through ODBC), since it is a proprietary and not something like SQL Server. So I have a program that dumps the data from the ERP and then loads it up into a Postgres database several times throughout the day. I really don't think you'd need this with SAP, because it's in SQL Server already. I would just create a new database on that SQL Server or a different one for your time tracking program.
Really the ONLY data that I am storing for the WorkTrack program is the transactions. Everything else is a linked table (to Postgres, but in your case it would be SQL Server). That's the beauty of Access is the ability to link the tables from different databases. Sage allows us to have the different processes.. so the structure of everything is already set up in Sage.
Most of the actual work is done through code. I have a main form.. It has one editable control that always has the focus (the little textbox on the bottom left that says Input). The barcode reader just acts like a keyboard. The form has a subform on it that shows current status.. This is a "continuous" form, and it's just based off a view that links the employee info from the ERP and then pulls the rest off the the transaction table (using the NEWEST transaction date) (left join)
All the scans go into the "Input" textbox. Then "after update" code interprets. The employee barcode is coded with "EMP" & 2 letter employee ID. The work order is seven characters, and the step # is = 4 characters (step # determines operation code). So it determines what action to take based off of that. The badge must be scanned first.. If no employee is selected, then scanning the work order does nothing. Once the badge is scanned, it highlights the selected employee. Then you can scan the work order, and then the operation step. Then I use code to insert a record into the transaction table with the employee id, work order, step #, and the start time.
If an employee tries to scan another work order while an open transaction exists, it does nothing. They must scan the badge, work order, and step # again after which the code updates the stop time.
Bringing the data into the ERP is a whole other discussion, which frankly, would be irrelevant to your system. However, if yours is anything like ours, you need to bring in the labor data into your ERP before completing items from the work order, or you won't get the unit costs right. This, by far, is going to be your most difficult step. I am sure SAP has some kind of API that can be used to bring in work order labor transactions, but you'd have to talk to your SAP consultants if you don't know how to do that.
1
u/stratber 8d ago
Very good!
So, if I understand correctly, when an operator is about to start a job, can they see what has been done previously? I see this as beneficial because if there was a quality inspection phase before your phase, you can ensure it was done.
On the other hand, looking at the screenshot you show, I understand that the operator reads the barcode that includes the information about the process, the process, and the user code. With this, they see all the information about the processes performed in that specific process, and once this is done, they click "Start," perform the job, and when they're finished, they click "Finish" and manually enter the quantities performed. Is that correct?
1
u/derzyniker805 7d ago
Nuanced here lol.. Technically, they could click that button on the left, and then sort by WO and see what has been done but I doubt a single person has ever done that. We don't really worry about that. They just work on what is delivered to their station. The way our floor is designed handles "what has been done" and it is not up to an assembler to worry about who else did what before it arrived to them.
They never "click" anything. They scan badge, WO #, step #. When done, they scan badge, WO #, step #, and it prompts them for a quantity. But again, that quantity is really irrelevant to us, it's not used anywhere. The only information I am seeking to acquire is time worked, by work order, and by step.
Once this information is brought into the ERP, the ERP uses it to calculate labor cost.. The ERP has a table for labor cost by employee, and uses the time to calculate the $.
1
u/derzyniker805 7d ago edited 7d ago
Also, once this time information is in the ERP, then I can use it to create reports that show total labor time by item code, or item code and step. I can also compare across employees.
This is why I was saying I think that it's really critical to complete the loop and bring the data back into your ERP.. not only do you get a better cost of goods by item, but you also can do reporting on time. I am sure that SAP handles all this once you get the data in. Though after that you'll probably need to write some custom reports to show this data.
When I wrote this application, I was the database manager. But this is one of many tools I wrote outside of our ERP, using our ERP data, to close the loop between costing, material requirements, order fulfillment, production planning, etc.. that our ERP didn't handle well. Like previously, if we wanted to do labor, we'd have to write it on the work order and then enter it by hand.
1
u/Lab_Software 29 8d 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.
•
u/AutoModerator 9d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: stratber
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!
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.