[AccessD] sql help please

Joe,

First I gave aliases to each table to make it easier to follow:

SELECT
  M.MyEmpNumb,
  A.ACCIDENT,
  A.MyAccidnetRecordNumber,
  A.CLAIM AS ClaimNumber,
  A.COMPANY,
  A.AccidentDATE,
  D.AccidentTime,
  D.AccidentDayofWeek,
  A.DRIV_NUM,
  A.REP_DATE,
  A.STATUS,
  T.TRMNL_DESC,
  C.START_DATE,
  A.OPEN_CLAIM,
  [last_NAME] & ", " & [First_Name] & " " & [Middle_Name] AS FName,
  M.DOB,
  W.RoadCondition,
  W.ROAD_TYPE,
  W.WEATHER,
  W.Loc_type,
  W.Acc_type,
  W.CHARGABIL
FROM (50_ltblTerminal AS T
  RIGHT JOIN (10_tbl_PersonelMasterData AS M
    INNER JOIN 10_stblEmpCompInfo AS C
    ON M.MyEmpNumb = C.MyEmpNumb)
  ON T.TRMNL_CODE = C.TERMINAL)
INNER JOIN (20_stb_Loc_Wather_Road AS W
  RIGHT JOIN ((20_tblAIMaster AS A
      INNER JOIN 20_stblAccDayInfo AS D
      ON A.MyAccidnetRecordNumber = D.MyAccidnetRecordNumber)
    INNER JOIN 20_stblAIPaidExpences AS X
    ON A.MyAccidnetRecordNumber = X.MyAccidnetRecordNumber)
  ON W.MyAccidnetRecordNumber = A.MyAccidnetRecordNumber)
ON (M.DRIV_NUM = A.DRIV_NUM)
AND (M.DRIV_NUM = A.DRIV_NUM);

Second, I noticed that the MyAccidnetRecordNumber field contained in 4
different tables is misspelled.  Do you get Access query prompts when
you run this query asking you to enter a value for
MyAccidnetRecordNumber?  If that's the case, then it looks to me like
the misspelling is the problem.

Third, I noticed that the clause (M.DRIV_NUM = A.DRIV_NUM) is shown
twice at the end.  I went to the trouble of creating your 7 tables
with enough fields to make the query work.  I noticed that by removing
the clause "AND (M.DRIV_NUM = A.DRIV_NUM)" from the end the query was
still valid.  Might that be your problem?

The table joins look straightforward enough.  You're associating
employees with accident reports.  Each accident report has an employee
code.  Accident reports must have data in both the
20_stblAIPaidExpences and 20_stblAccDayInfo tables, but data from the
20_stb_Loc_Wather_Road is optional.

I see that there are no fields SELECTed from the 20_stblAIPaidExpences
table.  Might that be a problem?  That is, no data in that table
causing no other data to be displayed?

Hope this helps.

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security



On 2/2/06, Joe Hecht <jmhecht at earthlink.net> wrote:
> 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
>