Well, I don't know that this is so bad. The only downside would be running a routine that expects a correlation when there may not be one... you'll have to decide.
MpoNum is an adequate key, especially the way this is set up. Time would be better spent serving up what your plant manager / stock controller are looking for. 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 The NOT operator above will pick up orphans on both sides of the relationship... if this is bad, use NOT IN (sel......) as Dennis described. 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 > >>>> > >>> > >>> > >>> > > > > >
