Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Jacksonville, Florida
    Thanked 0 Times in 0 Posts

    duplication finding formula (9.0/2001)

    In Access I have a formula that states: iif(dcount("dwg_no","detailed plan","[dwg_no]","[dwg_no]='"&[dwg_no]&"")>1,0,1) it is supposed to look at all items(txt) listed in the dwg_no field, and if they're repeated then return a zero, else 1- the problem is I want the first time the item shows up to be considered 1 and there after 0- in Excel I would use the formula: being that cell 1 of "original" =1, column "a" being the list of drawing #'s : =if(isna(match(a2,a$1:a:1,0)),1,0)
    Is there away to use a formula to get the same result in Access?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: duplication finding formula (9.0/2001)

    Tables in Access are different from tables in Excel, records do not have a specific built-in order. The "first time the item shows up" is entirely determined by the sort order you impose on the data. To do what you want, you need a way to find out if an item shows up for the first time; if there is an easy criterion for this, you can use that. For example, let's say that your records are sorted by a number field ID (ascending). The expression would become<pre>IIf(DCount("dwg_no","detailed_plan","dw g_no='" & [dwg_no] & "' And ID<" & [ID])>0, 0, 1)</pre>

    The ...And ID<" & [ID] part excludes the record under consideration from the count. We have to compare to 0 instead of 1 now, because of this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts