sql - Join table query returns multiple results from right table -

i'm new access , have pulled tog ether query in gui.

i have join set customers have complained in left table , work completed in right table.

both table have customers reference number i've joined based on that.

customers may complain before we've carried out work table left join.

the problem have customer may have had multiple work items, when create join each row in complaints table being returned multiple times if have had more on work item carried out.

i need add logic joins brings single work item each complaint. multiple work items present want query return work item date closets date of complaint never after it.

if complaint has no work items occur before still want complain shown data work item.

i know how use designer gui sql looks @ moment:

    select distinct [cear326 master data fo access1].id,      reportingcategory.reportingcategory,      [cear326 master data fo access1].category,      [cear326 master data fo access1].[sub category],      [cear326 master data fo access1].[contact partner],      main_tbl_serviceorder.serviceproduct, sotype.sotype,      format([actual date from],"mmm-yy") [date],      [cear326 master data fo access1].[actual date from],     main_tbl_serviceorder.basicstartdate,      [cear326 master data fo access1].smart_flag      (([cear326 master data fo access1]      left join main_tbl_serviceorder on [cear326 master data fo access1].[contact partner] = main_tbl_serviceorder.businesspartnernumber)      left join reportingcategory on [cear326 master data fo access1].category = reportingcategory.category) left join sotype on main_tbl_serviceorder.serviceproduct = sotype.serviceorder      (((main_tbl_serviceorder.basicstartdate)<="actual date from" or (main_tbl_serviceorder.basicstartdate)="0" or (main_tbl_serviceorder.basicstartdate) null))       order [cear326 master data fo access1].[contact partner], main_tbl_serviceorder.basicstartdate; 

i believe select distinct isn't working required due line

main_tbl_serviceorder.serviceproduct, sotype.sotype,  

this want statement come in , select relevant service order looking @ date of service order in relation date of cear [actual date from].

i think there issue current statement returning multiple service orders if occur before cear. i'd want return 1 closest cear date.

this uses

1. complaints table complaintid key , fields customer , complaint_date
2. workorders table workorderid key , fields customer , workorder_date

i assumed there 1 work order per customer per date. if isn't true, may need use autonumber field workorders instead of date field in selection criteria

you should able customize table names , field names.

select complaints.complaintid, complaints.customer, complaints.complaint_date, workorders.workorderid, workorders.workorder_date complaints left join workorders on complaints.customer = workorders.customer (((workorders.workorder_date) null or (workorders.workorder_date)=(select max(workorder_date) workorders t t.customer=complaints.customer , t.workorder_date<complaint_date)));