Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: How do you find both ISNull and IsNotNull records in same query

    I have a access DB that has a main table (Workorders) and a table (WorkordersParts) . There is a form (WorkordersForm) that the tech's use to log their calls and the data is populated in the Workorders table. Within that form they can also click on a button that opens the second table WorkordersParts only if they used parts for that call.

    I built a Query that wil allow them to find their calls within a time range . The problem is the query only pulls up the records that has data in the WorkordersParts table. I need it to show both records with or without parts.

    Here is the SQL code from the query:

    SELECT Workorders.Technician, Workorders.[Down Time], Workorders.[Repair Time], Workorders.[Date Started], Workorders.Issue, Workorders.SerialNumber, Workorders.Module, Workorders.[Corrective Action], Workorders.ProblemDescription, Workorders.DateFinished, Workorders.DateReceived, Workorders.EmployeeID, [Workorder Parts].Item, [Workorder Parts].Quantity, [Workorder Parts].Description, [Workorder Parts].WorkorderID, [Workorder Parts].Bin
    FROM Workorders INNER JOIN [Workorder Parts] ON Workorders.WorkorderID = [Workorder Parts].WorkorderID
    WHERE (((Workorders.Technician) Like "Todd Green") AND ((Workorders.DateReceived) Between Now() And DateAdd("h",-[enter hours],Now())));


    I have also attached a screen shot of my query in design view if it helps. What do I need to change to make this query produce all records with or without parts ?

    Thanks
    Rick
    Attached Thumbnails Attached Thumbnails Query.bmp  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If you want to retrieve all rows from Workorders but only those matching from [Workorder Parts] you need to change the type on join. Try: ... FROM Workorders LEFT JOIN...
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    3

    That works



    So Far that looks like it is going to work. I need to do some more testing but it looks good.

    Thanks
    Rick

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    3

    Now I need help with the results of the query

    Now that I have that part working It's time to move on to the next issue.I run a report to show a range of calls from the WorkOrders table and any calls that have parts will be on the list. The problem is that if you use four parts on one call or ticket it repeats all of the data for that same call. Its hard to explain so I will show examples below of how it looks and how I would like it to look. I'm not sure if I could fix this in the query or the report itself. I also attached screen shots of the report and the query results.

    This is how it looks now: For this example I will not include all fields , just enough to make my point. Notice the first three row's are the same call (WorkorderID), but it has several items used. The fourth record only has one item and the last line has zero parts used.

    Machine Problem Solution WorkOrderID Item QTY

    MX1234 No Power Replaced supply 2345 987654 2
    MX1234 No Power Replaced supply 2345 567839 4
    MX1234 No Power Replaced supply 2345 999912 1
    MAX998 Broken Belt Replaced belt 9987 ABctre 1
    MX0189 Overheating Removed cover 1111

    This is the same data but how I need it to look, or something similar

    Machine Problem Solution WorkOrderID Item QTY

    MX1234 No Power Replaced supply 2345 987654 2
    567839 4
    999912 1
    MAX998 Broken Belt Replaced belt 9987 ABctre 1
    MX0189 Overheating Removed cover 1111

    The only difference in the first three rows is the fields from the WorkorderParts table (Item, QTY) . Other than that its the same record.

    Please see attached


    Thanks
    Rick

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Built-in Sections and Group By features Access Reports provide allow you to do that very easily. Using the Report Wizard will give you a good start.
    Have a nice day!

Posting Permissions

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