Update text in one column based on first intance of data in two other columns

[yosko1]

I have two tables, #1 has a spending estimate column and table #2 has columns with Vname, Color, Part and Spending estimate. Need to update the #2 table with the information from Table #1 but only the first time in a row that Vname and Color are the same.

TbL #1
Name Color SpendEst
Dart Yellow 2500.00
Dart Red 3000.00
Arrow White 2000.00
Arrow Blue 1000.00



Tbl #2
Name Color Part SpendEst
Dart Yellow Door 2500.00
Dart Yellow Handle
Dart Yellow Rest
Dart Red Handle 3000.00
Dart Red Rest
Dart Red Mirror
Arrow White Door 2000.00


Arrow White Rest
Arrow White Mirror
Need to update the SpendEst in Tbl#2 with the SpendEst information in Tbl #1, but only the first time that the Name and Color in Tbl#2 match with the Name and Color in Tbl#1. The following rows for SpendEst column should remain blank

[June7]

What exactly determines which record is 'first'? Access records do not have inherent order (a table is not a spreadsheet). Must use data as criteria to select and order records.

[Micron]

just in case those are real field names, Name is a reserved word and should not be used for an object name.

I agree with the ordering sentiment. I know you can get something that resembles the expressed goal using a Select query, but if it uses aggregate functions (such as First) you can't use it in an update query, so I don't see that working. You'd probably have to create a table with it first:

SELECT [table2].[Name] & " " & [table2].[color] AS NameColor, Min(Table2.Part) AS MinOfPart, Table1.SpendEst
FROM Table1 INNER JOIN Table2 ON (Table1.Color = Table2.Color) AND (Table1.Name = Table2.Name)
GROUP BY [table2].[Name] & " " & [table2].[color], Table1.SpendEst, Table2.Name, Table2.Color;

Name	Color	MinOfPart	SpendEst
Arrow	White	Door	2000
Dart	Red	Handle	3000
Dart	Yellow	Door	2500

This might appear to work, but as June7 says, there's no guarantee of the order of a set of table records. You'd have to use a select query with an Order By clause or have a table field expressly for ordering to be safe.