Is there a way of using a calculation field to tell me how many times a
value has been used/entered in the same field across all the records?

I have a field and i need to know when the value has been entered 3
times or more in previous records...  so i am basically looking for a
count function.

So if field NAME contains the same name 3 times or more.

I have been looking through the functions list but cant seem to find
one that will do this, as always, any advice is greatly appreciated.

Gavin

You may use the Count function :
You may first create a selfrelationship with 'Name' as link, and a field 
'Constant' (calc) = 1. Then do NoOfCount (calc) = 
Count(YrLink::Constant).
For example looking for Duplicates I do :
Duplicate (calc) = Case( Count(Yr Link::Constant) > 1, "Duplic"; "")
The only problem I have doing that is that if there are the same value 
twice, for example, both records have the word 'Duplic" in the 
'Duplicate' field. Which one to choose deleting ? So I have two ways to 
continue :
- I add another field named 'test', that I empty first, then I choose by 
hand the extra copy(ies) I want to delete in putting an "X" in 'Test'. 
Then I search for "X" in Test and delete all found records. A little 
complement to this is that I fill 'Test' in all Duplicate records with 
an "X", and I choose the reverse ie I take away the "X" in the ones I 
want to keep. It's quicker if there are many duplicates for the same 
field (here 'name'). I often make a little script named 'FlipFlop' 
assigned on that 'Test' field that puts an X if there none and a none if 
there is one, so just clicking on 'Test' puts an "X" or removes the 
existing one. I can import those little scripts almost as is from file 
to file.
- I make a little more complex script, where I first 'find' all 
Duplicates, I sort them by 'Name', put the value of 'Name' into a global 
gName, then I make 2 loops one inside the other : the first to see if 
Name = gName, the 2nd to skip the first and put an "X" in 'test' of the 
next found in the loop,and also SetField(gName,Name), etc. to close both 
loops. Easier to do than to explain. I can send an example to anyone who 
wishes so.
Nevertheless, the best is to avoid duplicates at record creation. But if 
you import ...
Last, there is another method given in the Help, which is supposed to 
isolate the other duplicates and not the first one, but I never got to 
make it work, as when set it puts 'duplicates' in all records (of course 
the word 'Duplic' disappears when hitting on it but it fools the user). 
Is there a trick I missed ?
Remi-Noel


"GJPeacock" <GJPeacock@gmail.com> a �crit dans le message de news: 
1141113376.377384.26690@p10g2000cwp.googlegroups.com...
> Is there a way of using a calculation field to tell me how many times 
> a
> value has been used/entered in the same field across all the records?
>
> I have a field and i need to know when the value has been entered 3
> times or more in previous records...  so i am basically looking for a
> count function.
>
> So if field NAME contains the same name 3 times or more.
>
> I have been looking through the functions list but cant seem to find
> one that will do this, as always, any advice is greatly appreciated.
>
> Gavin
> 

Thanks for your reply, the only issue is that i don't actually mind
that there are duplicates. I'm not trying to delete them, i'm just
trying to count how many may exist and point out the total in a field
on the same form.

Anyone know how to do this?

Thanks

GJPeacock <GJPeacock@gmail.com> wrote:

> Anyone know how to do this?

Doesn't a self relation of this file on the field you want to test, with
a 'total('one')' count of a calculated field 'one=1' give you what you
want?

ard

In article <1141113376.377384.26690@p10g2000cwp.googlegroups.com>,
"GJPeacock" <GJPeacock@gmail.com> wrote:

> Is there a way of using a calculation field to tell me how many times a
> value has been used/entered in the same field across all the records?
> 
> I have a field and i need to know when the value has been entered 3
> times or more in previous records...  so i am basically looking for a
> count function.
> 
> So if field NAME contains the same name 3 times or more.
> 
> I have been looking through the functions list but cant seem to find
> one that will do this, as always, any advice is greatly appreciated.

There's two basic ways to do this, depending on your needs, and the
Count function is what you want in both cases:

A.  Summary Field
    Create a Summary field:
   
        s_Number of Name      Count(Name)

    Then you can create a Layout along the lines of:

           Header     {if needed}
           ---------------

             [Name]      [s_Number of Name]

           Sub-Summary (Trailing, Sorted by Name)
           ---------------

           Footer     {if needed}
           ---------------

     Where [] denote fields.

     Now it's a simple matter of finding the records you want to
     count (any variety of names), sort them by Name and then
     Preview or print this layout and you should get something
     like:

          Barney Rubble       2
          Fred Flintstone     3
          Papa Smurf          5




B.  Self-relationship
    Create a new relationship to the same table / file using
  
         Name = Name

    You can then use the Count function to count the number of 
    records related to the current one.
    ie.
         Number of THIS Name = Count(Relationship::Name)





The self-relationship is often the best if you want the number to
appear on-screen without Previewing / printing, although you can do the
same using the Summary field and the Get Summary function to obtain the
Summary field's value for each record.




Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

May I repeat the first 3 lines of my post ?
>>
You may use the Count function :
You may first create a selfrelationship with 'Name' as link, and a field
'Constant' (calc) = 1.
Then do :  NoOfCount (calc) = Count(YrLink::Constant).
>>
It should do the work, and you may forget about the rest of my post.
Remi-Noel


"GJPeacock" <GJPeacock@gmail.com> a �crit dans le message de news: 
1141147220.510343.120600@i39g2000cwa.googlegroups.com...
> Thanks for your reply, the only issue is that i don't actually mind
> that there are duplicates. I'm not trying to delete them, i'm just
> trying to count how many may exist and point out the total in a field
> on the same form.
>
> Anyone know how to do this?
>
> Thanks
>