Query returns only the first record from the table

[simmerdown]

I'm not sure if this problem is originating from my Access database or the ColdFusion app, but here's what's going on.<br><br>Within the database I have a table named &quot;Departments&quot; which contains two columns, &quot;ChemID&quot; and &quot;DeptName&quot;. The two columns together make up a compound key for that table, since there can be duplicates in either ChemID or Departments (but never both in the same row).<br><br>My query is:<br><br><FONT FACE=monospace>&lt;cfquery datasource=&quot;rtk&quot; name=&quot;id&quot;&gt;<br>SELECT ChemID<br>FROM Departments <br>&nbsp;WHERE DeptName = '#URL.dept#'<br>&lt;/cfquery&gt;</font><br><br>The DeptName value gets passed in the URL from the previous page, of course.<br><br>My recurring problem is that <b>the query only returns the first record</b> that matches my DeptName. There should be multiple rows selected.<br>I've tried several variations on that query with no success, and queries elsewhere with an identical structure work just fine. Am I lacking something in my SQL, or should I go back to the database? I'm beginning to think it's the database at the root of the problem.<br><br>Any help is much appreciated.

 

[simmerdown]

I've gotten it taken care of ... the queries were getting a little too complicated, and an <b>inner join</b> was what was needed. The final working query is:<br><br>&lt;cfquery datasource=&quot;rtk&quot; name=&quot;chems&quot;&gt;<br>SELECT Chemical.ChemName, Chemical.ChemID, Departments.ChemID<br>FROM Chemical, Departments<br>&nbsp;WHERE Chemical.ChemID = Departments.ChemID AND DeptName = '#URL.dept#'<br>&nbsp;&nbsp;ORDER by Chemical.ChemName<br>&lt;/cfquery&gt;<br>