self joint table count failed

[shermund]

hi guys, first post ever. Been trying to look around for this reference but i can't seem to find the keyword for this.

I am migrating from Access to Filemaker. still very new in this.

i have 1 table with 3 fields

Visiting log and visiting log_self where it joints Date equal date, Name equal name and PersonInCharge equal PersonInCharge

id Name date PersonIncharge flag=if(id=visiting log_self::id, "a","") Totalclient=count(visiting log_self::name) Month=Month(date)

1 john 4/4/2012 Sandy a 2 4

2 John 4/4/2012 Sandy 2 4

3 Jason 4/5/2012 Sara a 1 4

4 Keith 4/6/2012 Sandy a 1 4

5 John 5/4/2012 Sandy a 2 5

6 John 5/5/2012 Sandy 2 5

actually what i wanted to get is,

Total client is to calculate how many clients of my staffs has visited us on each month, the result should be,

id Name date PersonIncharge flag=if(id=visiting log_self::id, "a","") Totalclient=count(visiting log_self::name) Month=Month(date)

1 john 4/4/2012 Sandy a 2 4

2 John 4/4/2012 Sandy 2 4

3 Jason 4/5/2012 Sara a 1 4

4 Keith 4/6/2012 Sandy a 2 4

5 John 5/4/2012 Sandy a 1 5

6 John 5/5/2012 Sandy 1 5

Explanation, on April, Sandy had only 2 clients visited us, though there are 3 entries, Sara had only one.

On May, Sandy had only 1 client visited us, though he came twice where sara had none.

Don't really know where went wrong or i am not even using the right function, tried using summary field count but it came back wrong too.

Please advise me on this, i may not have explaned this very clearly but hoping that it is not too confusing. Sorry.

regards

[imoree]

post a sample file, it would be easier as i don't follow what you said...

[shermund]

realized that the formating of the post went into a mess. i have attached a sample file here.

I wanted to calculate how many of the personincharge's client has came on the monthly basis. not the total visit numbers

count.zip

[imoree]

maybe this will help..

Sorry, i posted the incorrect file; THis new file has JUST RELATIONSHIP for your needs.

: (count2.fp7.zip

Edited April 8, 2012 by imoree