Results 1 to 7 of 7

Thread: Help with Query

  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: Help with Query

    Thanks in advance for your help!!!!

    I have equipment that I need to track performace after scheduled maintenance (PM) is completed. The information is entered by Equipment number for line and shift. There is a column that contians a numeric value for when a PM is completed (0 = no pm, 1 = pm). All PM is completed on 1st shift. I need to get performance for second shift when a PM is completed on first shift. For example.

    Select Production/Potential as Performace, LineNum, Shift, EntryDate
    Form tblDailyProduction
    Where Shift = 2 AND [a PM has occured on 1st shift]
    Group by LineNum, Shift, EntryDate

    I just haven't been able to pull out shift 2 on the days that PM is = 1.

    I tried to use
    Select Production/Potential as Performace, LineNum, Shift, EntryDate
    Form tblDailyProduction
    Where EntryDate = (Select EntryDate From tblDailyProduction Where PM = 1)
    but this did not work as the subquery returned multiple records error.

    Here are the table columns:

    Name Type
    ProdID int
    EntryDate Date/Time
    LineNum int
    Shift int
    Dept Text
    EquipType Text
    ProductType Text
    Potential Number (Long)
    Production Number (Long)
    PM Int
    DateCode Text

    Thanks,
    Lee

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Code:
    Select tDP1.Production/tDP1.Potential as Performace
         , tDP1.LineNum
         , tDP1.Shift
         , tDP1.EntryDate
      Form tblDailyProduction tDP1
      join tblDailyProduction tDP1 on tDP1.ProdID = tDP2.ProdID
                                  and tDP1.LineNum = tDP2.LineNum
                                  and tDP1.EntryDate = tDP2.EntryDate
     Where tDP1.Shift = 2
       and tDP2.Shift = 1
       and tDP2.PM = 1
    my assumptions are:
    ProdID = EquipmentID
    EntryDate does NOT have a time component.

    post back if this is not true
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Yes, EntryDate does not have a time component.
    No, ProdID is not the same as equipment number. It is an id for the record. LineNum is the identifier for each piece of equipment. Do I need to set LineNum = LineNum instead of ProdID = ProdID?

    Thanks so much for the quick response,
    Lee

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Thumbs down

    caught another mistake...

    Code:
    Select tDP1.Production/tDP1.Potential as Performace
         , tDP1.LineNum
         , tDP1.Shift
         , tDP1.EntryDate
      Form tblDailyProduction tDP1
      join tblDailyProduction tDP2 on tDP1.LineNum = tDP2.LineNum
                                  and tDP1.EntryDate = tDP2.EntryDate
     Where tDP1.Shift = 2
       and tDP2.Shift = 1
       and tDP2.PM = 1
    clear as mud?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Yes, I made the change and it gave the results (once I change Form to From).

    Now that you solved this problem, could I bother you to tell me exactly what is happening so that I can take this "knowledge" that you have so kindly shared and use it in other times of opportunity?

    thanks again!!!!!!!!!!!!!
    Lee

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Ah, set theory, it's wonderful stuff...

    tDP1 gives you a list of all equipment that was used on shift 2.
    tDP2 gives you a list of all equipment that had maintenance pulled on the first shift.

    Take the intersection of the two lists, via Equipment ID and activity date, to get your answer.

    Now don't ask me why I can't spell "FROM" but remember this kind of stuff after being out of school for 20 years!
    Last edited by Paul Young; 05-07-03 at 16:37.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    THANKS FOR THE POWER!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •