Data entry at point of manufacture.

[HillTJ]

People, i'm in need of direction that may not point to access as the best option. I work in manufacturing (i think that occupation is nearly dead). We currently capture quality measurements manually on a form. I them copy the data into a bunch of excel spreadsheets. It's taking a lot of my time now as we're capturing more data, have less staff etc etc. Seems to me that it is silly to hand write data, then manually enter later. I wish to have some method of operators entering data directly. We have wifi in the factory, but it would be difficult (read company won't spend the money) to get a LAN cable out to the factory. My boss is excel mad ever since i showed him how to make a data entry form, and loves his own work. It's pretty crap really from a logical point of view and he's made no provision for backups, logons etc and all the stuff that makes a proper application.

Now, i've written several applications that work really well, but don't have the experience to recommend or develop a sensible solution for this application, nor am i conversant with other options . Our IT people are unwilling or not understanding what i need, so no assistance there.

What should i do?

 

[jdraw]

Seems the boss is blindly making/not making (read that as ignoring) any sort of data management discipline. Without backups, organized logons etc it's a problem (potential disaster) waiting to happen. As you know it's when, not if. This is probably a major reason for the decline/demise of manufacturing.
As you may be aware, Access does not play well in wireless network --- so wired or other software solutions (non Access) may be necessary. But, bottom line is, you need a "patron" (someone in management with authority) to see the light and devise a small project to show some simple benefit(s) of more discipline and focused automation. You need to get "Buy-in". You can't use the 1960-70's methods today and remain competitive. Data capture should be a byproduct of performing a process, it should not be a series of separate manual tasks - in my view.

What would happen if several of these spreadsheets were lost/deleted in error?
What is the time lapse between the quality measurement and its use in the manufacturing process? Is it timely or a root cause of "spoiled batches" or whatever?

I'm sure many of us have been is analogous situations. You can try to resolve ( but the patron approach is better than any totally bottom up effort) or seek other employment. Key question is "How important is the situation to a) the company, and b) you?"

 

[Pat Hartman]

Without WiFi and some data entry device at each workstation, what advice could we give you? Whether you use Excel or Access is moot without the infrastructure to actually capture data.

The one thing you might want to mention to him is that Excel is a single user solution. If he has multiple people doing data entry, he needs something more robust and Access is a good start.

Even cheap laptops will cost around $800. Hardware for dirty places is custom made so that it doesn't get dust inside the case. I don't know the cost but think thousands instead of hundreds.

 

[The_Doc_Man]

The question is whether someone is willing to consider at least some automation in the gathering process. Nothing is free, but there is such a thing as putting a spreadsheet on an Android cell phone. If you could make data gathering happen to a spreadsheet (with a predefined format), you could then use the WiFi network to e-mail the complete sheets for import to a central system. You would pay some licenses for mobile Excel (if that was the way to go).

If this makes any kind of sense to you, then you can do research on your own using this question to your web browser: "Can I use Excel on an Android phone?" - you will get a bunch of pages on the ins and outs of the process. Developing a pre-defined spreadsheet format to assure uniform data capture would be the starting point for you, and this might be the way to go.

This would have the effect of avoiding the ugliness of Access over WiFi. Excel via phone would be using simple file transfers that, if the operation failed, could be retried without corrupting Access. Once you have a valid spreadsheet transferred to your workstation, ImportSpreadSheet to a simple Access DB would be the next logical step.

If even that is too much for management to consider, then I don't see another way out from where I'm sitting.

 

[Gasman]

I use Excel on my phone free of charge?

 

[The_Doc_Man]

Probably true, @Gasman, but the articles I read said that such an ability is extended only to non-business users.

 

[EdwardsC]

Maybe get a TOUGHBOOK for the auditor / person collecting data. The data collected on the toughbook can be exported or shared on a main desktop DB via bluetooth. The file can be imported to the main computer DB so both are synced. I work in quality manufacturing and this was a good solution to gathering a lot of clean data for KPI reports.

TOUGHBOOK 33

The 12.0” Panasonic TOUGHBOOK® 33 is the first fully-rugged 2-in-1 of its kind—redefining rugged flexibility for those who work in demanding conditions. TOUGHBOOK 33 features a groundbreaking 3:2 display, making it easier to handle, while improving readability of the business applications and...

na.panasonic.com

 

[HillTJ]

EdwardsC, that would be my preference too. I'd elect to have a couple of toughbooks at workstations where measurements can be entered into an access database. Clearly, the'd have to be connected to the network via LAN cables. It's a long distance from out router to the plant location but IT people know this stuff & it shouldn't be an issue in practice. I suspect my boss will push for a couple of cheap tablets and introduce some kind of excel form. The excel workbook will become huge very quickly. I don't think this project can be undertaken without assistance from a professional. I cannot see my way round that. Developing an access app is a lengthy process for me, thus the excel focus by my boss who himself could not develop an access app.

The_Doc_Man, we currently use data tables in excel and just add the new records on, we filter to get production graphs, stuff like measurements per product, production run, time. That bit works pretty well.

 

[EdwardsC]

There’s not enough context to understand whether or not MS access is the proper solution. If you decide to invest time in learning and developing, you will also save a lot of time through automation. If WiFi access is an issue and IT approves you can order a Mobile hotspot device.

 

[HillTJ]

EdwardsC, the factory has WiFi coverage, but I'm not going to suggest an access solution with the BE connected via WiFi for clear and obvious reasons. Excel, by virtue of loading the entire workbook into memory is a bit independent of a wifi connection, except for loads/saves. Cheers

 

[The_Doc_Man]

@HillJT - concur with your viewpoint that a "local" Excel operation followed by a Wi-Fi-based whole file transfer is probably the best of the possible in your context. USUALLY when you do a whole-file transfer you can tell if it made it into place.

As to appending incremental tables to extant tables (whether Excel or Access), if it works reliably enough to not kill your business then pragmatism dictates that "a man's gotta do what a man's gotta do." With no particular apologies to Sly Stallone.

 

[Pat Hartman]

If you end up with Excel at the point of collection, import into Access and do everything else from Access. Do NOT allow Excel to become the system of record.

 

[HillTJ]

Pat, i've been thinking long and hard about this. Until you responded i was thinking of collecting the data from each machine using an android tablet & excel and have the excel files saved to a cloud location. Then have a master excel document that the individual tablets data is exported. I worry about how huge the 'master' will become & i too would prefer importing into access as the database size would be no longer a concern. Plus, IMHO excel is not designed to be used as a database, non-relational, flat file, no inbuilt toolset for reports etc, etc. Excel is different!.

I'm really stuck with this and fear my boss will railroad me into excel. He will always cite the old chesnut of access not working over wifi, as the excel files are stored in a cloud location he can open them remotely.

He is unfamiliar with access, but can get a basic spreadsheet it work.

I really don't know how to combat this.

 

[Cronk]

Store all the data in Access via Excel and then export anything that your boss wants to Excel.

 

[Pat Hartman]

Unless your boss is going to be the developer or he gives you a direct order to use Excel for everything, use the tools you have to create something that will protect the company's precious data. Excel is NOT a database. PERIOD. Using Excel to collect the data isn't a terrible solution. Leaving the data in Excel is what is terrible.

You have to make the data from Access accessible to the boss man without him having to know anything about Access. That means that you will create reports that take arguments so he can slice and dice at will. If he wants pretty charts, export the data to excel and build him the charts. Access no longer has a good charting function although you can try. Maybe it will be sufficient. And if all else fails, give him the ability to export ranges of data to Excel where he can play to his heart's content and not mess up anything in the database.

 

[HillTJ]

Gents, You have been very clear. Thanks.

 

[HillTJ]

The Boss says it can all be done in excel with an ORIGINAL spreadsheet run on each of several Tablets or Raspery Pi (i've no experience with this but he likes them), & saved to a cloud drive. He doesn't believe it's a good idea to use excel for data capture only & unnecessary to append data into a secure application!. Instead a monolithic excel spreadsheet somehow. He wins! & develops it himself. I expressed my strongest concern that this is not right & move on.

Pretty much what Pat aluded to in the first paragraph of post #15!

 

[Pat Hartman]

The boss is a control freak. He doesn't understand relational databases but he considers himself an expert with Excel so he's going to show you how great he is. I would distance myself from this endeavor. You don't want to be tainted by it. If you are the IT expert of the group, find a new job.

 

[HillTJ]

Stay Tuned!