Select multiple tables

Results 1 to 3 of 3

Thread: Select multiple tables

  1. #1
    Randy Guest

    Default Select multiple tables

    I am trying to create a SQL statement where I pull data from 5 separate tables in a database. I have tried many way to get this statement to work:<BR><BR>rsOutcome2.Source = "SELECT [00COMMUNITY].FY00_Q0026SortofEasy, [99COMMUNITY].FY99_Q0026SortofEasy, [98COMMUNITY].FY98_Q0026SortofEasy, [97COMMUNITY].FY97_Q0026SortofEasy, [96COMMUNITY].FY96_Q0026SortofEasy, [00COMMUNITY].CODE, [00COMMUNITY].BUILDING, [00COMMUNITY].GRADE<BR>FROM 00COMMUNITY INNER JOIN (96COMMUNITY INNER JOIN (97COMMUNITY INNER JOIN (98COMMUNITY INNER JOIN 99COMMUNITY ON ([99COMMUNITY].GRADE = [98COMMUNITY].GRADE) AND ([99COMMUNITY].BUILDING = [98COMMUNITY].BUILDING) AND ([98COMMUNITY].CODE = [99COMMUNITY].CODE)) ON ([98COMMUNITY].GRADE = [97COMMUNITY].GRADE) AND ([98COMMUNITY].BUILDING = [97COMMUNITY].BUILDING)<BR>AND ([97COMMUNITY].CODE = [98COMMUNITY].CODE) AND ([97COMMUNITY].CODE = [98COMMUNITY].CODE)) ON ([97COMMUNITY].GRADE = [96COMMUNITY].GRADE) AND ([97COMMUNITY].BUILDING = [96COMMUNITY].BUILDING) AND ([96COMMUNITY].CODE = [97COMMUNITY].CODE)) ON ([00COMMUNITY].GRADE = [99COMMUNITY].GRADE) AND ([00COMMUNITY].BUILDING = [99COMMUNITY].BUILDING) AND ([00COMMUNITY].CODE = [99COMMUNITY].CODE)<BR>WHERE ((([00COMMUNITY].CODE)=&#039;" & Replace(rsOutcome2__varCODE, "&#039;", "&#039;&#039;") & "&#039;) AND (([00COMMUNITY].BUILDING)=0) AND (([00COMMUNITY].GRADE)=&#039;Total&#039;));"<BR><BR>In the WHERE clause I am pulling data from a form on a previous page that says what the CODE number is. varCODE is a Request.Form statement that gathers the info from the form.<BR><BR>I am getting a "Expected end of statement" error in my WHERE clause. Does anybody see anything wrong with it??<BR><BR>I appreciate any help!<BR><BR>Thanks,<BR>Randy

  2. #2
    Nils Bilhorn Guest

    Default RE: Select multiple tables

    You need an on condition after each inner join, it is not possible to clump them togehter at the end.<BR><BR>I.e.<BR> select * <BR> from a join b on a.c1 = b.c1 join c on b.c1 = c.c1 and b.c2 = c.c2<BR><BR>There is no need for all the parenteheses you are using. <BR>

  3. #3
    Randy Guest

    Default RE: Select multiple tables

    I actually just cut-and-pasted the SQL code directly out of Access and just changed where I am getting the first part of the WHERE clause from (a form instead of an input box in Access).<BR><BR>The problem seems to be with the WHERE clause...but I&#039;m not sure where it is???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts