I think im about there now, just got to test tomorow, heres what I had to do. I had to write the whole thing in SQL not just the two tables i wanted to link:

 

SELECT dbo_wireact.crtd_date, dbo_wireact.mo_nbr, dbo_wireact.prod_desc, dbo_wireact.mach_nbr, dbo_ROUTING_OPERATION.COST_CENT_NBR, ([RUN_RATE]*1000)/60 AS [COSTED MPM], [dbo_wireact]![good_qty]/([dbo_wireact]![run_hrs]*60) AS [ACTUAL MPM], [ACTUAL MPM]/[COSTED MPM]*100 AS diff, dbo_wireact.good_qty, [dbo_wireact]![scrp_setup]+[dbo_wireact]![scrp_ip]+[dbo_wireact]![scrp_cut] AS SCRAP, dbo_users.forename, dbo_users.surname, dbo_ROUTING_OPERATION.COST_TYPE
FROM ((dbo_wireact INNER JOIN dbo_PRODUCT ON dbo_wireact.prod_desc = dbo_PRODUCT.PROD_DESC) INNER JOIN dbo_ROUTING_OPERATION ON (dbo_PRODUCT.PROD_ID = dbo_ROUTING_OPERATION.PROD_ID) AND (dbo_wireact.wcnt_nbr = dbo_ROUTING_OPERATION.COST_CENT_NBR) AND (dbo_wireact.oper_nbr = CSTR(dbo_ROUTING_OPERATION.OPER_NBR))) INNER JOIN dbo_users ON dbo_wireact.oper_id = dbo_users.user_id
WHERE (((dbo_wireact.crtd_date) Between #1/1/2009# And #1/1/2010#) AND ((dbo_ROUTING_OPERATION.COST_CENT_NBR)="523" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="520" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="546" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="502" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="505" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="509" Or (dbo_ROUTING_OPERATION.COST_CENT_NBR)="503") AND ((dbo_ROUTING_OPERATION.COST_TYPE)="B"));

 

Unfortunatly when I came to try this I forgot that two of the tables are comming from different ODBC sources.

 

so im going to add this to the top

FROM [odbc;dsn=datasource;database=pubs;Username= <username>;PWD=<strong password>].ROUTING_OPERATION,[ [odbc;dsn=datasource;database=pubs;Username= <username>;PWD=<strong password>].wire_act

 

ill let you all know how I get on