Join with multiple matches, select first match

[EveatEden]

Here's my problem.<br>I've got 2 tables with a lot of the same fields.&nbsp;&nbsp;I am using a SQL query to compare those shared fields and join the matching records into a results table.&nbsp;&nbsp;<br><br>What I've had happening is that there is only 1 record in Table A, but 3 records in Table B whose shared fields match the fields in Table A's 1 record, but have slight differences in other fields which can't be used for comparison.&nbsp;&nbsp;Therefor, the 1 record in Table A is being combined with each of the records in Table B and results in a 3 record Table C (results table).&nbsp;&nbsp;Basically a Cartesian product. <br><br>What I want to happen is for only the first record in Table B that matches the record in Table A to be joined and copied to Table C, but I can't seem to find a command that would do that.&nbsp;&nbsp;<br><br>Does anyone have any ideas? <br><br>Thanks,<br>Eve

 

[THoey]

I have found two different ways, depending on the flavor of SQL you are using.&nbsp;&nbsp;&nbsp;If you are using Oracle, you can use the rownum value to limit the number of rows returned:<br><b><br>select a.test, b.test<br>from a, b<br>where a.test = b.test and <br>rownum &lt; 2<br></b><br>If you are using MS Access, there is a MaxRecords property that can be used in your VB code that uses the query. <br><br>I am sure there are other ways, just the first two I found. <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>

 

[EveatEden]

I am using MS Access.&nbsp;&nbsp;Would that limit the records in the table to 1 or only limit the matches to 1?&nbsp;&nbsp;the numbers I used were examples.&nbsp;&nbsp;The real tables contain much more data than just 1 row in one table and 3 in the other.&nbsp;&nbsp;<br><br>Thanks,<br>Eve

 

[THoey]

According to MS Access help (My comments follow):<br><br>You can use the MaxRecords property to determine or specify the maximum number of records that will be returned by a query that returns data from an ODBC database.<br><br>Setting<br><br>The MaxRecords property setting is a Long Integer value representing the number of records that will be returned.<br>You can set this property by using the query's property sheet or Visual Basic.<br><br>When you set this property in Visual Basic you use the DAO MaxRecords property.<br><br>Remarks<br><br>Records are returned in the order specified by the query's ORDER BY clause.<br>You can use the MaxRecords property in situations where limited system resources might prohibit a large number of returned records.<br><br>My Comments:<br><br>From what I can tell, it would only effect the number of rows returned, not the number of rows in the table.&nbsp;&nbsp;&nbsp;If you pull up MS Access and search for help on MaxRecords, you might be able to find more info.&nbsp;&nbsp;&nbsp;If not, I have a book that I could look up more when I come in tomorrow.<br><br>Good Luck...<br><br> <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>