How to update the first occurence of a specific field?

[lifter10]

I'm fairly new at Access and need a little bit of help. I'm using Access 97 to do an update query, SQL is below:

UPDATE (FileNames INNER JOIN Final ON FileNames.Company = Final.company) INNER JOIN OutputTable_by_field ON Final.Cusip = OutputTable_by_field.Field2 SET OutputTable_by_field.Field10 = [Field10]-[Final]![Total], OutputTable_by_field.Field15 = [Field15]-[Final]![Total], OutputTable_by_field.Field17 = [Field17]+[Final]![Total], OutputTable_by_field.Field16 = [Field16]-[Final]![Total]
WHERE (((Final.[Aloc Status Type Code])=4));

It's a pretty basic update as I'm just adding/subtracting certain fields. However, Field2 in my OutputTable may have more than one occurence of the same value. I cannot uniquely identify the records in the OutputTable other than using an autoid. So, how can I update only the first occurence of a specific value for Field2? Thanks in advance. Chris

 

[fmarckus]

Hi Chris,

A simple way around this is to first create a temporary table using the Query Wizard, (Make Table Query). You should select records from the OutputTable using a Group By clause to ensure Field2 is unique. By making Field2 in the temporary table a primary key, you can join this to the other relevant tables to make an updatable recordset or update query.

 

[lifter10]

Unfortunately I don't think I can do that and get the answer I want. Each record of the database is unique. I'll give a little background to explain. These records are actually stock/bonds that are sold during a certain period and Field 2 is their Cusip (identifer). A stock can be sold more than once during a period, hence having more than one record for the same Field 2 value. I cannot combine the sales together for each cusip for reporting reasons, but I need to do a calculation based upon the first occurence only of sale. I'm probably making things more complicated than it really is. I would do it by hand but there are thousands of records.