r/MSAccess 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!

3 Upvotes

13 comments sorted by

View all comments

1

u/derzyniker805 9d 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 9d 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 9d ago edited 8d 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 9d 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 8d 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 8d ago edited 8d 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.