MS Access ODBC DB Structure

[oxicottin]

Hello, today I was asked to create a DB from a ODBC table that is updated every 5 minutes from our PLC which I successfully linked to. I am in no way involved in creating this ODBC table named (tblDelay) our IT department is and is taking care of that and the PLC stuff, I'm just asked to create a DB to show data or display. Anyway, the data im asked to show is cumming delays by employee and these are fields given to me by the ODBC (tblDelay)

DelayID PK
LineNumber (Machine number delay occured) Actual copied data: 7
TimeDelayBegin (Time delay started) Actual copied data: 6/3/2014 1:33:00 AM
TimeDelayEnd (Time delay ended) Actual copied data: 6/3/2014 1:36:00 AM
DelayHours (Hours/Minutes that occured) Actual copied data: 0.05
AlarmCodeId (Delay reason code) Actual copied data: 2109



Table I created that coincides with AlarmCodeId (tblDelayReason)

AlarmCodeId PK
DelayReason (Text reason for delay) Actual copied data: Operational Steel Splice


I just cant figure out how I can add an employee into the mix?






Thanks,
SoggyCashew.....

 

[SkipVought]

Hi,

Maybe there's another table that has recorded the employee assigned to a machine during a period of time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[oxicottin]

No there isnt another table, the employee doesnt enter there name in anywhere for the PLC to capture so I just have a list of shifts and dates that have delays. Im just thinking how in the heck I can do this...

On a side note I also needed to display each (LineNumber) for todays date and yesterdays date in a query what would the criteria formula be? Date() AND Date()-1

Thanks,
SoggyCashew.....

 

[PHV]

what would the criteria formula be
BETWEEN Date()-1 And Date()

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

When you state, "there isnt another table" do you mean 1) these is no other data that has been downloaded into a table via a query using an ODBC driver (cuz there's no such thing as an ODBC {Open Data Base Connectivity} [highlight #FCE94F]table[/highlight]), or are you saying 2) that no such data exists ANYWHERE in your PLC, which I seriously doubt.

I'm relatively certain the the latter is the case. Which means that you probably need yet another query via the ODBC method, to access that data. So here's what you need to be prepared to do. 1) get access/permission to access the PLC directly with reason only, 2) verify or obtain the correct ODBC driver for your PLS database (Oracle, DB2, etc) 3) configure your ODBC driver. Now you're ready to access/query the PLC to gather all the data you'll need to relate, to answer the questions at hand.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[oxicottin]

Ok, lol Skip im new to this ODBC sceen so bare with me.... I was given some pictures and a table password on how to get the table created by my IT from the PLC so acually I had to YouTube on how to do it and thats how I got it working... Anyway, I know what information the PLC is gathering because what the operators enter as delay and when the machines shut down and turn on the operator never enters thier name of who is running the machine, that is logged on paper....

Thanks,
SoggyCashew.....

 

[madonnac]

Unfortunately, there's no ODBC driver for a paper table. It needs to be entered into the computer...

Are they expecting you to set up a webcam so you can take a picture of who is operating the machine?
Perhaps OCR scanning so you can read the paper they log the user on?

With the information they have given you, I don't see a way to capture the operator, without having the data entered into a computer somewhere on the network (I add this because I've had people ask why I can't grab data from a stand alone PC).

 

[SkipVought]

It would seem to me that somewhere in your machine scheduling db (is that the PLC?) there is an employee assigned to a machine with a clock-in and clock-out.

But then again this could be a departmental function where anyone in the dept could change machine settings, in which case, the granularity of the data is less precise.

You can only report what is accessible in the system.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[razchip]

Do you have some type of labor gathering system along with the machines PLC program. Seems that a company trying to gather this type of information would also have a labor tracking device that would link to the PLC.

Thanks for the help.
Greg

 

[oxicottin]

I have asked to have a way to capture the employees name even if its an id num and i can match it up in a table before going any further. Thank you for your posts...

Thanks!

Thanks,
SoggyCashew.....