How can I find FIRST record that meets criteria ?

[jackiev]

Joining table1 to table2 in a one to many relationship.<br>Want to update a field in table1 with value in a field from FIRST join record found in table2 in a query?&nbsp;&nbsp;Any ideas? Using Access97.

 

[kathryn]

There is a way to return the top record in a query, and that's probably what you would use&nbsp;&nbsp;BTW, referring to the first record returned can be dangerous, so it is best to find a field that you can sort on that will return the records in the order you want.<br><br>If I understand you, you have say, 50 records in table1 and 500 records in table2 and you want to update a field in table1 with the first record in table 2 which has a matching join field.&nbsp;&nbsp;I really can't see how to do this in a query, unless your first record returned from table2 is first by virtue of having a minimum or maximum value of all the records in table2 which are linked to table1.<br><br>I'm not sure if I am making myself clear, but if I am, let me know exactly how you are defining &quot;first&quot; record and we'll see if it can be done in a query. You may have to use code, but it wouldn't be too bad.<br><br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jackiev]

example:<br>TABLE: CUSTOMERS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;¦&nbsp;&nbsp;&nbsp;TABLE: Purchases<br>Cust#&nbsp;&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;History&nbsp;&nbsp;&nbsp;¦ Cust#&nbsp;&nbsp;item&nbsp;&nbsp;&nbsp;date<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Jim Smith&nbsp;&nbsp;_______&nbsp;&nbsp;&nbsp;¦&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dog&nbsp;&nbsp;&nbsp;01/01/00<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;John Doe&nbsp;&nbsp;&nbsp;_______&nbsp;&nbsp;&nbsp;¦&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cat&nbsp;&nbsp;&nbsp;05/03/00<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Allen Jet&nbsp;&nbsp;_______&nbsp;&nbsp;&nbsp;¦&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fish&nbsp;&nbsp;06/07/00<br><br><br>Query to update CUSTOMERS.History with the first item that was purchased by this customer (in this example-dog for Allen Jet).<br><br><br><br>

 

[kathryn]

I think you can do this using a subquery.&nbsp;&nbsp;I just got slammed with work, so I can't go into it myself, but check out the help file on Subqueries and see if that seems to do what you want. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[cmmrfrds]

Try using DISTINCT as the predicate in your SQL statement. Look under help for &quot;SQL DISTINCT&quot;.

The SQL will look something like this.

Select DISTINCT CUSTOMERS.CUST#, Name from CUSTOMERS
WHERE CUSTOMERS.CUST# = Purchases.CUST#
ORDER BY Purchases.date


THe DISTINCT will return a record for any field value that is different in the select statement. So, don't include date in the select statement or it will return a record for each date, but the ORDER BY will put the lowest date first -- assuming ascending.

 

[jackiev]

Thanks--that worked great. Wish I had posted this earlier.This forum is the BEST reference for new progammers and developers. Thanks to all who share the knowledge!!!