<!--- Our Access database is in "data" folder --->
<CFSET DBPath=ExpandPath("../data")>
<CFSET DBFile="ows.mdb">
<!--- Build ODBC Connection String --->
<CFSET Driver="{Microsoft Access Driver (*.mdb)}">
<CFSET ConStr="DRIVER=#Driver#;DBQ=#DBFile#;DefaultDir=#DBPath#;UID=Admin;PWD=;">

<!--- Retrieve Films and Related Actors from database --->
<CFQUERY NAME="GetFilmInfo" DBTYPE="dynamic" CONNECTSTRING="#ConStr#">
  SELECT 
    Films.FilmID, Films.MovieTitle, 
    Actors.NameFirst, Actors.NameLast
  FROM Films, FilmsActors, Actors
  WHERE Films.FilmID        = FilmsActors.FilmID
    AND FilmsActors.ActorID = Actors.ActorID
  ORDER BY Films.MovieTitle, Actors.NameLast
</CFQUERY>

<HTML>
<HEAD><TITLE>Films and Actors</TITLE></HEAD>
<BODY>
<H2>Films And Actors</H2>

<!--- Display retrieved information in HTML table --->
<CFTABLE QUERY="GetFilmInfo" HTMLTABLE BORDER COLHEADERS>
  <CFCOL HEADER="Film ID" TEXT="#FilmID#">
  <CFCOL HEADER="Film Title" TEXT="#MovieTitle#">
  <CFCOL HEADER="Actor (First Name)" TEXT="#NameFirst#">
  <CFCOL HEADER="Actor (Last Name)" TEXT="#NameLast#">
</CFTABLE>

</BODY>
</HTML>
