On my third cup... been past the jug alot today. We have one other person who works with me on the network and support features and she is on vacaction today and tommorrow, so I have been covered up.
Will get time to study your and Dennis' solutions in a test environment most likey next week, maybe. Still a lot to do between now and then.. Oh well, trudging on..
I really appreciate all the great adive on this.. Jim Limburg
Ben Petersen wrote:
Ohhhhh Jim... :(
I shouldn't touch my keyboard before my second cup of coffee.
Forget this:
Create view JobStatus (MpoNum, JobStatus) as + Sel Distinct MpoNum, 'Started' + from HdrTable t1, TimeTable t2 + whe t1.MpoNum = t2.MpoNum + Union + Sel Distinct MpoNum, 'Pending' + from checks t1, checkdtl t2 + whe NOT t1.TranId = t2.TranID
Do this:
Create view JobStatus (MpoNum, JobStatus) as + Sel Distinct MpoNum, 'Started' + from HdrTable t1, TimeTable t2 + whe t1.MpoNum = t2.MpoNum + Union + Sel MpoNum, 'Pending' + from checks + whe MpoNum not in + (Sel Distinct MpoNum from TimeTable)
Sorry,
Ben Petersen
On 20 Mar 2003, at 8:40, Jim Limburg wrote:
Our users don't use the R:> Most can just use applications to do specific enter/edit/delete processes.. They can access it, but none would know hot to accomplish anything. Truth be told they have full access to almost all RBase functionality, they never venture farther then allowed.
Basically, this system was designed to have the header information entered when the jobs are issued by our plant manager or stock controller. Then when the jobs actually get started which could be a time from of hours to months.. the big flaw here .. and plant workers clock in and out on these jobs the system drops rows in as the work is in progress/ and or completed.
As far as common key.. the system was designed to be based soley on MpoNum. The data in the TimeTable is pulled directly from a file created by a C program that pulls it from our DOS timeclock system. It would be difficult, maybe not impossible, to effect the data coming in from the time clocks.. I guess it could be done if we loaded it into another table then manipulated it and then put it into the TimeTable... hmmm
Thanks for the thought process, sometimes it's better to just take the time and think about this.. hard to do when you are a programmer, network admin, hardware support, and the user help desk for several companies, and somewhere in there get to do db work.. ha,ha
The man with many hats, and to small of head for them all Jim Limburg
Ben Petersen wrote:
Sorry Jim, I didn't get that, but Dennis did. You might consider coding in such a way as to not allow orphans. If people are working from the R> this could be tough, but if you're writing an app, insert a common key to both tables. For the next time entry, check for an unused row and edit it - or - do another insert and then edit. Saves tones of grief.
Ben Petersen
On 19 Mar 2003, at 16:20, Jim Limburg wrote:
I hope I made myself clear on the last post. Basically I was thinking.. What if there we no records in the Timetable table so there would be way to compare mponum to mponum...
Jim
Ben Petersen wrote:
Hi Jim,
From the view, or either table, you could could test for null in one or
more
columns:
Sel * from HdrTable t1, TimeTable t2 + whe t1.MpoNum = t2.MpoNum and + t2.WorkHrs is Null and t1.ItemNum is Null
Ben Petersen
On 19 Mar 2003, at 15:51, Jim Limburg wrote:
G-Day all
I would like some advice.
We have one table which is basically a header table for MPO to track jobs in the plant.. then another table that gets data from timeclocks for each Mpo number and related data..
I want a view and ultimatley a report that I would run on this view to show all the mpos that do not have time put onto them. In other words Mpos entered into the system, but not yet started on..
General info in the header table I would like to collect would be Mponum, Itemnum, ShrtDesc,QtyOrd,Location,shopordr
and then the table of time tracking we have Mponum,WrkDate,WorkHrs,OTHrs,DTHrs,ClockNo
Can someone give some suggestions to break this fog I'm in..
I know this is so simple it's going to make me kick my can when I see it, but I've had one of those head in the cloud days..
Thanks for input Jim Limburg
