Results 1 to 2 of 2
2004-02-10, 12:22 #1
- 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?
2004-02-10, 12:49 #2
- 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.