Manufacturing shop floor production info database project

[Skibber]

Hey all,



I am currently working on a production cycle time study for 5 work stations in one of our assembly departments. I have a PLC capturing cycle times from each station and recording the times into an array. I also have a WinXP box with Access 2003 running a software package that connects the PLC to a database. For testing purposes, I set up 1 table to capture the cycle times for one of the work stations. It currently had 3 fields..."Transaction" ID, Time Stamp, and Cycle Time.

Everything works great so far, but I would like to get some recommendations as to how I "should" set up my database. I've been wondering if I should set up a table for each station (WS1_CycleTime, WS2_CycleTime, etc) or add some fields to the existing table (CycleTime) to distinguish the entries from each station. I would like to plan for adding other PLC's in the future to capture data from other departments and don't want to run into a wall later and wish I had done things differently. I am familiar with Access as I have used it in the past, but by no means proficient as I haven't developed anything from scratch. I do have a pretty steep learning curve though.

My plans are to develop a local web app to view the data and find out things like min, max, avg, std of cycle times in a given time frame once I get the database design all sorted. I already have the web server configured on the box as well.

Any feedback would be greatly appreciated.

Thanks,
Steve

[JamesDeckert]

>> if I should set up a table for each station (WS1_CycleTime, WS2_CycleTime, etc)
no, you should have a field in the table contains the workstation#, PLC's could be another field.
You want to stay away from creating the same table structure multiple times (Bad!!!).
Maybe find a tutorial on table normalization.

[NTC]

you want all stations writing to a single table and to common fields - you may need a 4th field to identify the station - - your explanation is not clear to me to be more specific

[Skibber]

Thanks for the feedback. I read up a bit on normalization and made the following changes:

tbl_CycleTime - Fields: Transaction (PK), Department (Ref department table), Workstation (Ref work station table), TimeStamp (When completed), CycleTime (Time to complete in secs from PLC)
tbl_WorkStation - Fields: Station Code (PK), Station Description, Target Cycle Time
tbl_Department - Fields: Department Code (PK), Department Description

I am currently working out how to know which transaction came from which work station though. Right now the PLC is only adding the cycle time data to a field in the table.

NTC...to be more specific, I am wanting to simply capture the cycle time data for each work station from the PLC and be able to run queries/reports (see OP) from a local web app. It's pretty much just an ongoing time study to monitor the production rates.

[Skibber]

The issue that I am having is that I can successfully map the cycle time tag value from the PLC to the cycle time field in the db, but I can't figure out how to get the work station and department fields in the table populated with the appropriate data. It would be nice to get them to auto-populate depending on the input from the PLC when the transaction is triggered. For example, when "WS1_Unit Time.Current" adds a record to the db, the Department Code field is populated with "DeptCode1" and WorkStation Code field is populated with "StationCode1". Can this be achieved with a reference lookup or filter of some sort?

Another option might be to create tag values for the department and stations, then map those fields as well in the transaction.

For what it's worth, I'm using an Automation Direct (Koyo) Productivity 2000 PLC/PAC and DataWorx software from BizWare Direct

[orange]

I don't know anything about the automation software you are using, but I would suspect there is some api or parameter that could be set to identify the workstation or PLC to identify the source of each transaction.
That's where I'd be looking.
Good luck.

[Skibber]

Orange...the write to db logging transaction is triggered from a different tag for each work station when a cycle is completed. WS1_Unit Time.Current, WS2_Unit Time.Current, WS3_Unit Time.Current, etc. Then in the DatawWorx software I map the tags to the fields that they populate.

[orange]

OK. Then what exactly is the issue?
A PLC writes a record that has WkStnID, and TimeStamp. And you know the mapping of WrkStnID to Dept.
What exactly are you missing? An example or 2 would be helpful.

[Skibber]

Currently the only field in the record that is being written by the PLC is the cycle time. The time stamp field is populated by the "Now()" command. I was hoping to have the WkStnID field auto populated by which tag in the PLC made the cycle time entry. So if WS1_Unit Time.Current populates the CycleTime field of the record then it automatically sets the WorkStation field to WrkStn1 and the Department field to Dept1. I think I may just create another tag in the PLC called WkStnID and map that to the WorkStation field in the transaction.

[orange]

Have you spoken to the software supplier/supporter?
Have you researched their FAQs and support tutorials/videos?
Have you tried to contact any other users of the software, or a user support group?

In my view, and I am not familiar with PLCs nor the software, there must be some parameters that you can set up/adjust/create to identify the PLC involved.

[Skibber]

Have done all of the above. The PLC has a "Tag Database" which is basically a collection of actions or data containers that are used for programming. In my case...the WS1_Unit Time.Current tag contains the data from a timer that counts the cycle seconds at the station. When a signal from a push button is sent to end the cycle, the data is written to an internal array that I programmed. That entry is also sent to the DataWork software via another command. The DataWorx software holds the config of where the data is coming from (which tag in the PLC) and where it's going (which field in the DB).

I guess I was just wondering if there was a way in access to create a data entry condition. IF field"X" is populated by source"A" THEN populate fields"Y"&"Z" with appropriate reference data.

Thanks for all of the input orange. I will continue researching options until I get something that works and can be built upon as I add more departments, stations, etc for data collection. Seems like I need to map a tag that populates the station field in the DB. Then maybe have a lookup that populates the department field.

[ItsMe]

@Skibber
You stated

Then in the DatawWorx software I map the tags to the fields that they populate.

and you also stated

I was hoping to have the WkStnID field auto populated by which tag in the PLC made the cycle time entry.

Why not have DataWorx map the tags into a normalized data structure that Access can link to or import? It sounds like you have already done this, at least to some extent.

[Skibber]

ItsMe...DataWorx is the connection between the PLC and the DB so Access doesn't really need to link or import anything. Sounds like you're referring to having DataWorx responsible for populating the work station field via a separate mapped tag as opposed to having the work station field in the DB populated by which tag the cycle time was populated by.

[ItsMe]

I have no experience with DataWorx. Like others, I am not understanding what the issue is. From my point of view, If you can use DataWorx to create an Array, you should be able to use DataWorx to create normalized data.

You mentioned that you were doing some programming in DataWorx to manipulate output. I may have quoted you out of context. I am, merely, wondering why you do not normalize the output. As you stated in post #13, DataWorx is the link. Perhaps DataWorx is an SDK that provides COM objects and what you are doing is using the COM objects to create this "Array".

What programming language and what development environment are you creating this Array? Where is this Array stored, in the computer's memory?

[Skibber]

The array is stored in the PLC. It was created by a ladder logic instruction. I do not know off hand what the environment is. There is a software package which runs on Windows that is used to program the PLC via a GUI. The DataWorx server runs in Windows as well. I am in the process of testing what you had suggested about just mapping another field for the workstation in addition to the cycle time.

Was I over thinking things in regards to having multiple fields in the DB populated by input of one field?