updating single row per group in access query

[sugat11]

Hi,



I need a query to update first row per each group with a vale of 1.
Example:
table1 have below fields

ID marks cnt
1 34
1 22
2 44
2 4
2 5
6 7

after query output should be
ID marks cnt
1 34 1
1 22
2 44 1
2 4
2 5
6 7 1


Please let me know your inputs. Advance Thanks

[CJ_London]

you have insufficient information to provide what you want, you need another field to indicate a unique order - perhaps a date or uniqueID, or maybe (based on what you have provided) it is the record with the highest marks per ID? - which can be used to determine the 'first' record.

[sugat11]

what will be the query

update table1
set cnt =1
where




need to update table 1 field cnt with 1 for any row in group

[CJ_London]

you still need to decide how you determine a single row from a unique value. In your data, marks might be unique but if two records had the same marks and ID there would be two records updated.

You could try

update table1
set cnt=1
where marks=dlookup("marks","table1","ID=" & ID)

but I'm nut guaranteeing it will work a s you require

[sugat11]

Thansk Ajax it worked:

Below is the input table :

month	site	marks
1	blr	56
1	blr	22
2	blr	11
2	blr	66
2	blr	88
1	hyd	11
1	hyd	55
1	hyd	6
2	hyd	1

what will be the query to get the output as below:

Month	blr	hyd
1	78	66
2	77	1

[CJ_London]

use a crosstab query - month - row header, site-column header - marks - value (sum)

[sugat11]

Much Thanks , you are a great help!!