Update Duplicate Records

[maximgor]

I have one field (column) called ID1 that has records 1,2,3,4,5,6 and so on. I have another field ID2 that has also records 1,1,1,2,2,1 and so on. I also have a field called amount1($) 100, 100, 100, 200, 200, 100. So, it could make raws such as 1 1 100 and 2 1 100 and 3 1 100. I need to create SQL statement that will create another amount (amount2) field or update already created field amount2 in a way that it will show 0 for duplicate records in amount2. The duplicate records are the ones that have the same ID2 and amount1 records. For example, if raws look like that: 1(ID1) 1(ID2) 100(amount1) and 2 1 100 and 7 1 100. I need to have 1 1 100 and 2 1 100 0 and 7 1 100 0. Thank you for your help!

 

[wiklendt]

huh? try to explain your problem again. it makes absolutely no logical (or other) sense.

also would be helpful if you give a little background to what you're doing, what/why you are trying to achieve and the things you already have tried to do in attempting to solve this.

just a stab in the dark - you could pull a select query into a report, then groupby whichever field you want and/or "hideduplicates"...

edit: by the way, welcome to the forums

 

[marianne]

yah.that is true.it would be better if you attach your db file in this forum and tell your problem and what do you want the result would be.

 

[honda882000]

If I understand it correctly, you are using a combination of ID2 and amount1 as a determinant of a unique record. I believe you will have to use two queries for this. You could create one query that groups by ID2 and amount1 and has a third field with count(ID2 or amount1). This first query should be able to give you the number of occurrences of the ID2/amount1 combination. You can then join this query to the original table by ID2 and amount1, in a second query. In this next query, create a new field: IIF(count from first query > 1, 0, NULL).

Hope this helps somewhat. Cheers.