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 > > >>>> > > >>> > > >>> > > >>> > > > > > > > > >
