Join Tables - Missing Data

[Adam McReynolds]

I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.

Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.

My problem is if I have the 2 tables joined then I only see records that match both tables. Any help or direction would be awesome!

Here is my SQL for the query:

SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn, IIf([incoming_disposition]="Warranty","Warranty",IIf([incoming_disposition]="BER","BER",IIf([incoming_disposition]="Completed","Standard","N/A"))) AS [Repair Type], tbl_module_repairs.problem_description, tbl_module_repairs.work_comments, IIf(IsNull([complete_date]),"Unit Not Completed ",DateDiff("d",[pickup_date],[complete_date])) AS [Turnaround Time], tbl_module_repairs_captured_data.Rate
FROM tbl_module_repairs INNER JOIN tbl_module_repairs_captured_data ON tbl_module_repairs.prikey = tbl_module_repairs_captured_data.record_num
WHERE (((tbl_module_repairs.pickup_date) Between [Enter Start Date] And [Enter End Date] And (tbl_module_repairs.pickup_date) Is Not Null And (tbl_module_repairs.pickup_date)>#1/1/2014#) AND ((tbl_module_repairs.complete_date) Is Not Null) AND ((tbl_module_repairs.prikey) Is Not Null) AND ((tbl_module_repairs.customer)="Cox SD" Or (tbl_module_repairs.customer)="Cox NC" Or (tbl_module_repairs.customer)="Cox OC" Or (tbl_module_repairs.customer)="Cox SB" Or (tbl_module_repairs.customer)="Cox PV"))
ORDER BY tbl_module_repairs.pickup_date;

 

[plog]

You'll need to do a few things:

1. Change your INNER JOIN to a LEFT JOIN (http://office.microsoft.com/en-us/access-help/left-join-right-join-operations-HP001032251.aspx). In design view, you right click on the linking line between the two tables and change it to show all records from tbl_module_repairs.

2. Change your WHERE clause to eliminate the exclusion of non-completed records. One of the criterion is that the complete_date cannot be null. I'm guessing that needs to be changed. Also, you might review the rest of your criteria to make sure non-completed records make it through.

3. In the SELECT clause you need a conditional statement similar to the [Turnaround Time] field you have. The criteria argument can stay the same, but the True/False results should be changed to show the Rate field if it exists and 0 if it doesn't.

 

[Adam McReynolds]

You'll need to do a few things:

1. Change your INNER JOIN to a LEFT JOIN (http://office.microsoft.com/en-us/access-help/left-join-right-join-operations-HP001032251.aspx). In design view, you right click on the linking line between the two tables and change it to show all records from tbl_module_repairs.

2. Change your WHERE clause to eliminate the exclusion of non-completed records. One of the criterion is that the complete_date cannot be null. I'm guessing that needs to be changed. Also, you might review the rest of your criteria to make sure non-completed records make it through.

3. In the SELECT clause you need a conditional statement similar to the [Turnaround Time] field you have. The criteria argument can stay the same, but the True/False results should be changed to show the Rate field if it exists and 0 if it doesn't.

Thanks Plog, that did it! My only problem now is how to make my new expression formatted to currency. I know how to do it on a regular field but the option is gone when it is an expression. Anyways, I will probably post that problem as it's own thread. Thanks again. Cheers!!