i have a contact database, it has leads, clients and referrals.

when you are in the referrals section, i would like a count of all clients and leads that have the referrals name in their referral section

say im in 'tommy' and he is a referrer. it will show 24 people he referred based on everyone who has 'tommy' in their referral field.

it would be awesome if i could do it by a month to month basis, based on their creation date.

and have that data go into a portal..

yea?

And the relational structure is recursive or what??

--sd

what do you mean

Describe your relational structure? I'm not pretty sure clients and referrals should go to separate tables, or that I'm not getting what referrals in the first place - aren't they some sort of persons too?

You need a join table called consultations/meetings or such, from where the statistics should take place!

--sd

well i attached the file.

theBE.zip

Im not sure I know the beat of the drum you follow relationally - explain design objectives and mission, including description of the legacy base.

--sd

Your business rules and structure affect the answer to your question.

For example Clients and referrals are both types of persons.

I don't know what a 'lead' is, but it sounds like it would be a potential client- therefore a person record.

If you just have a "Persons" table, you are going to have a little recursion, because you will be relating the person table to itself.

All of that aside, the way I would do it (which may not be correct) is to at the very least put both leads and clients in the same table with a field that designates which they are. Relate this table to the Referrer table (which can just be a table occurence of the original table). You will not want to use the Name as the key to this relationship, but a serial number. You can set the entry field to use a value list that shows only the names from the desired table, but actually enters the serial key.

Than just create a calc field : Count (keyfield). Make sure the key is from the client TO and the calc is evaluating from the referrer TO.

see attached

matt

reeferer_Madness.zip

You can add the function of doing it by month, but you will have to use a multicriteria relationship between the tables.

good luck,

matt