Ms-access – Manipulating duplicate records in access

ms access

I have a table with data as follows:

Field1  Field2 Field3
10      10     20
10      20
10      30
1       0
20      1      1
20      1
20      1 
30      2      2
30      2
30      2

My goal is to find duplicate values of field1, and if said field is a duplicate, to average field2's data into field3. For instance Field1's duplicates are 10, thus Field 3's value should return 20.

How would I go about doing this?

To the best of my knowledge, something similar of Java would be written as follows (bear with me my Java is rusty)

{ int field1 = field1;
  scanner.in();
  int field2 = field2;
  scanner.in();

  int sum = sum;

  int count = count;

  int field3 = field3;

  while (field1.hasnext());
  {
  if(field1 = field1)
    {
        sum = sum+field2;
        count++;
    }
else
    {
        out.print("null");
    }
    }

    field3 = sum/count;

    System.out.println(field3);
    }

Best Answer

If you're just trying to query the table to get the average;

 Select AVG(field2) as Field3 from Table where EXISTS (Select Count(field1) from 
 table Having Count(field1) >1)

If you want to view all of the other columns in your query results you'll need to use a self join to calculate the average, or a group by clause. You didn't give an example of what you wanted in your results so I can't help much further.

Related Question