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

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

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

Thanks
Rick

You're welcome!

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

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.