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

    Unanswered: Help I have gone brain dead

    I have create a table called production. Where production information is entered on each shift for each day (3 shifts). When I first created the table, there was no need to track equipment without production. Now it is!! What I need to do is be able to query the table by line, shift and date for a date range and determine which lines, shifts and date are missing production data. The machines are numbered from 2 to 12 and the column name is LineNum. I have columns named Production, Shift, and EntryDate. I know that I am probably going to feel stupid when I see the fix, but for the life of me, I just don't see how to do it without recordset and then processing it in vbscript.
    Thanks in advance,
    Lee

  2. #2
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    Lee, I'm not sure if I eally understand the problem, but it sounds like the following

    SELECT LineNum, Shift, EntryDate
    FROM production
    WHERE Production IS NULL
    AND EntryDate BETWEEN @begindate AND @enddate


    @begindate and @enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    If you can, post here the tables structure in order for us to have a good understanding of your situation.

    ionut

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

    Question

    I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.

    SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept

    FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine

    GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine

    HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))

    tblNumLine
    NumLine

    tblProd
    LineNum
    Shift
    Production
    EntryDate

    Thanks,
    Lee
    Last edited by clinel; 04-25-03 at 15:11.

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

    Unhappy Still Brain Dead

    Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.

    Thanks to all for your patience with an knucklehead,
    Lee

Posting Permissions

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