[LEMarshall]

I’m trying to place into a grid, data from several related tables in a sequence from a start date to end date. There are several forms that have to be completed that are related to completion of work activities on a well rig. We identify the Lease the well is on and the well by its number. The types of forms are: LOL =Left on Location; MTF =Material Transfer Form; BJSA = behavioral Job safety Analysis; MTF = Material Transfer Form.
Activity starts when the rig operator fills out an LOL form or Crew supervisor fills out an MTF. Once an LOL or MTF is generated it auto increments a variable intLastFormNum which is assigned to it and the BJSA and MMF that follows which is the relationship between the tables.
I want to find all LOL and MTF forms from start date to end date and show the related BJSA and MTF even if those forms were started OUTSIDE the start/end dates. There can be more than one LOL or MTF per well during the time frame.
I want to display them in the grid with the columns being the Lease name, Well number, the start date of the LOL (or blank if MTF), then the start date of the MTF (or blank if LOL), the form number, the start date of the BJSA, and the start date of the MMF. Both the BJSA and the MMF may be blank – not started yet (in the gird, I’m going to color code yellow if 3-7 days after the other forms start date or red if 8 or more days since the other form was started).
The attached bitmap shows the table relationships.
I’m not getting the all of the forms showing up yet, let alone trying to narrow it to just the start/end date. I’m sure it has to do with the either/or nature of the LOL or MTL form. One or the other of these forms will cause the creation of the other two forms- but not both. In either case the BJSA and MMF forms will have the same PreFormNum that either the LOL or MTL form has.
SELECT l.ecoLeaseID, l.Name, w.ecoLeaseID, w.ecoWellID, w.WellNumber, o.ecoWellID, o.FormRefNum, o.DateCreated, t.ecoWellID, t.FormRefNum, t.DateCreated, b.FormRefNum, b.StartDate, m.PrevFormID, m.MovementDate
FROM ((ecoLease AS l INNER JOIN (ecoLOLForm AS o INNER JOIN ecoWell AS w ON o.ecoWellID = w.ecoWellID) ON l.ecoLeaseID = w.ecoLeaseID) INNER JOIN ecoMTLHeader AS t ON w.ecoWellID = t.ecoWellID) INNER JOIN (ecoBJSAHeader1 AS b INNER JOIN ecoMMFHeader AS m ON (b.FormRefNum = m.RefNum) AND (b.PrevFormID = m.PrevFormID)) ON (o.FormRefNum = b.FormRefNum) AND (t.FormRefNum =b.FormRefNum);

Any help is greatly appreciated.
Larry

Attached File(s)

•  sqlRelationships.bmp (279.71K)
  Number of downloads: 39