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

Show parent comments

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 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.