Update the First Record in a Table

[janmack79]

Hi I have duplicate records and I am trying to figure what I would enter for the criteria in and Update query. If you look at my screen print I want to enter a number "1" in the field Opp Count for the first occurrence of an Opportunity Number in that same table.

[rpeare]

Run a query

SELECT [OPPORTUNITY NUMBER], MIN([DATE CREATED])
FROM [LENGTH TO CLOSE DETAIL]
GROUP BY [OPPORTUNITY NUMBER]

link this query back to your LENGTH TO CLOSE DETAIL on BOTH the opportunity number AND the created date. This will identify which records need to be updated. Depending on how you progress from there you may be able to run an update query or manually update your table.

Some things to keep in mind
1. Don't use spaces in your field/table names
2. Don't use special characters other than underscores (_) in your object/field names (i.e. name your table Length_to_Close_Detail rather than including spaces)
3. Read rules on data normalization

[janmack79]

The Date Created is not unique that is duplicated as well, there is more than one Product on some the opportunities so Min of Date created would not be helpful I believe because it would still populate a 1 for all the records?

[rpeare]

then how are you going to identify the 'first' record for each opportunity number?

[janmack79]

I guess that is my question, are you not able to just identify the first occurrence of a record based on the first time it appears to update another field? Or is not that simple

[rpeare]

If you are using an autonumber primary key, you can identify the first record to get into your table
if the records were entered sequentially you could go based on the minimum value in that autonumber table.

The best method though would be to sequence them as the data enters the table rather than try to do it after the fact.

If you don't have an autonumber primary key it doesn't really matter which was the 'first' record if you have five on the first day what does it matter which one you pick as 'first'