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

There is a way to return the top record in a query, and that's probably what you would use  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.

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.  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.

I'm not sure if I am making myself clear, but if I am, let me know exactly how you are defining "first" 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.

Kathryn

example:
TABLE: CUSTOMERS           ¦   TABLE: Purchases
Cust#  Name      History   ¦ Cust#  item   date
1     Jim Smith  _______   ¦  3     dog   01/01/00
2     John Doe   _______   ¦  1     cat   05/03/00
3     Allen Jet  _______   ¦  3     fish  06/07/00


Query to update CUSTOMERS.History with the first item that was purchased by this customer (in this example-dog for Allen Jet).

I think you can do this using a subquery.  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.

Kathryn

Try using DISTINCT as the predicate in your SQL statement. Look under help for "SQL DISTINCT".

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.

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!!!