Nick
nick at frasiervan.com
Sat Feb 4 18:57:10 CST 2006
That's a big nasty query. Two items just looking at it. Is this field name correct? [20_tblAIMaster].MyAccidnetRecordNumber You have a join to 20_stblAIPaidExpences but are not viewing any fields, nor needing any field in this table for an implied relationship. Previous advice applies, build this query step by step and figure out at which stage of rebuilding it doesn't work. From: Joe Hecht [mailto:jmhecht at earthlink.net] Sent: Thursday, February 02, 2006 8:35 PM To: 'Access Developers discussion and problem solving' Subject: sql help please I am having 1 issue with the statement below. I have confirmed 20_stb_Loc_Wather_Road does indeed have data in it so we are not looking at null messing me up. ( I think) When I run the query no data from the table 20_stb_Loc_Wather_Road is populating the query. I tried splitting the query into a second query with everything except the 20_stb_Loc_Wather_Road on one side and the 20_stb_Loc_Wather_Road fields as the second query source and I still had the same problem. I will check in morning. Bed time for me now. SELECT [10_tbl_PersonelMasterData].MyEmpNumb, [20_tblAIMaster].ACCIDENT, [20_tblAIMaster].MyAccidnetRecordNumber, [20_tblAIMaster].CLAIM AS ClaimNumber, [20_tblAIMaster].COMPANY, [20_tblAIMaster].AccidentDATE, [20_stblAccDayInfo].AccidentTime, [20_stblAccDayInfo].AccidentDayofWeek, [20_tblAIMaster].DRIV_NUM, [20_tblAIMaster].REP_DATE, [20_tblAIMaster].STATUS, [50_ltblTerminal].TRMNL_DESC, [10_stblEmpCompInfo].START_DATE, [20_tblAIMaster].OPEN_CLAIM, [last_NAME] & ", " & [First_Name] & " " & [Middle_Name] AS FName, [10_tbl_PersonelMasterData].DOB, [20_stb_Loc_Wather_Road].RoadCondition, [20_stb_Loc_Wather_Road].ROAD_TYPE, [20_stb_Loc_Wather_Road].WEATHER, [20_stb_Loc_Wather_Road].Loc_type, [20_stb_Loc_Wather_Road].Acc_type, [20_stb_Loc_Wather_Road].CHARGABIL FROM (50_ltblTerminal RIGHT JOIN (10_tbl_PersonelMasterData INNER JOIN 10_stblEmpCompInfo ON [10_tbl_PersonelMasterData].MyEmpNumb = [10_stblEmpCompInfo].MyEmpNumb) ON [50_ltblTerminal].TRMNL_CODE = [10_stblEmpCompInfo].TERMINAL) INNER JOIN (20_stb_Loc_Wather_Road RIGHT JOIN ((20_tblAIMaster INNER JOIN 20_stblAccDayInfo ON [20_tblAIMaster].MyAccidnetRecordNumber = [20_stblAccDayInfo].MyAccidnetRecordNumber) INNER JOIN 20_stblAIPaidExpences ON [20_tblAIMaster].MyAccidnetRecordNumber = [20_stblAIPaidExpences].MyAccidnetRecordNumber) ON [20_stb_Loc_Wather_Road].MyAccidnetRecordNumber = [20_tblAIMaster].MyAccidnetRecordNumber) ON ([10_tbl_PersonelMasterData].DRIV_NUM = [20_tblAIMaster].DRIV_NUM) AND ([10_tbl_PersonelMasterData].DRIV_NUM = [20_tblAIMaster].DRIV_NUM); TIA Joe Hecht jmhecht at earthlink.net -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com