Ignore Duplicates when updating Table

[brendanolear]

I have a table of unique sales codes.

I have another table of Sales Values recorded against the unique Sales Codes.

I wish to create an update table that contains all unique Sales codes and adds the Sales Values against the Sales Codes, however I only wish to capture the first instance of any record where a Sale has occured.

The unique Sales table contains 3000 rows I wish it to remain at 3000.

(Much the same way that a lookup in excel would only show the first occurence).

Is this possible in Access and if so what join properties etc do I require between my two tables.

[jackd]

What fields are in each of the tables?

I only wish to capture the first instance of any record where a Sale has occured

First on what basis?

[brendanolear]

hi Jack d - basically if there are multiple sales against a unique sales code for example. 30,50,80 I wish it to return any instance but only once.

If it requires to allocate the highest or lowest value this would suffice, the important factor is returning only one result.

Field in table one are Sales Code - customer etc and a currently blank Sales Value
Field in Table two are Sales Code and Sale Values

[jackd]

I mocked up your data and tables as follows

...........................................................................
Table4 structure
CustomerNo 0 Long 4
SalesCode 1 Long 4
SalesValue 2 Currency

Table4 Data

CustomerNo SalesCode SalesValue
1 1 0
2 2 0
3 3 0
420 4 0
........................................................................
Table5 structure
SalesCode 0 Long 4
SalesValue 1 Currency


Table5 data
SalesCode SalesValue
1 23
1 35
1 10
2 20
2 50
2 80
3 7
4 10
4 50
5 0.04
....................................................................................................................

I used this query

UPDATE table4 as A
INNER JOIN table5 as B ON [a].[SalesCode] = [B].[SalesCode]
SET [a].[SalesValue]  = [B].[salesValue]

To give this Update to table4

CustomerNo SalesCode SalesValue
1 1 10
2 2 80
3 3 7
420 4 50