Access Unique Count

[spherehunter]

I'm trying to add a value of 1 in a new column for every unique value in lm_filler 2 and 0 for the duplicates. Can this be done?

[June7]

You want a 1 for the first occurence of Im_filler2 and a 0 for each repeat? What determines the first - seqno? Not sure an UPDATE query can do this. Might require VBA.

[spherehunter]

You want a 1 for the first occurence of Im_filler2 and a 0 for each repeat? What determines the first - seqno? Not sure an UPDATE query can do this. Might require VBA.

Correct in a new field. Seqno can be used to determine the first occurrence.

[June7]

Here is what I find:

I can build a query that will assign a count value to each record in each Im_filler2 group:
SELECT seqno, Im_filler2, (SELECT Count(Im_filler2) +1 As CountIM FROM Table1 As Dupe WHERE Dupe.seqno<Table1.seqno AND Dupe.Im_filler2 = Table1.Im_filler2) AS Category
FROM Table1;

Then I attempt to use this query to update a new field in table (I manually added to table a field called Cat):
UPDATE Query1 INNER JOIN Table1 ON Query1.seqno = Table1.seqno SET Table1.Cat = IIf([Query1].[Category]>1,0,1);

That generates error, as I expected it would, 'Operation must use an updateable query.'

What you can do is make a table from the first query then use it in place of the query in the UPDATE.

[spherehunter]

I got it to work with this:

Unique: IIf(DCount("*","MTD Call","[lm_filler2]=" & Chr(34) &
[lm_filler2] & Chr(34) & " AND [Seqno]<" & [Seqno])=0,1,0)

[June7]

Wonderful! If it's not too slow. Domain aggregate functions can be very slow with large datasets. I never looked at DCount for this, always went with the subquery. A new trick in my bag. Thank you.