Counting number of records in a certain category.

[scchoi]

Hello,

I have a question on how to create a field with the calculation (or any other suggestions) that would display a field in my database that shows a list where i'll have the number of items per category.

In other words, let's say I have 3 different tables.

table 1: default home page

table 2: lists of sports

table 3: sports team

in my default home page, i have a portal shows the sports team on left column and the lists of sports the team is apart of.

now what I want to do is instead of showing the sports team on the left column, i want a field that counts the number of teams in each sports. IE:

Number of Teams Sports Category

4 Basketball

5 Football

2 Baseball

3 Hockey

Note that each teams are given a separate sports category already from their respected tables/fields such as a relationship between Category and Teams.

[comment]

Note that each teams are given a separate sports category already from their respected tables/fields such as a relationship between Category and Teams.

I am not sure I read this correctly: how many categories can one team belong to? And how exactly is a team related to its category/ies?

[scchoi]

It's one team for one category.

So my relationship goes something like this: Home_Team_Sports

and each team is assigned a certain sport.

what i'm doing is creating a function where i count the number of teams per sport and having that number displayed.

I'm not so sure if I should use a calculation or summary field here, or if i ned a script. I used a summary calc field and got the portal to display the category (in this case it's 11) but on the side of the category, instead of displaying idfferet numbers it shows 11 for all; so it's counting up all the categories instead of counting up the records related to the category.

hope that makes sense.

[comment]

Assuming your relationship is:

Teams::CategoryID = Categories::CategoryID

define a calculation field (result is Number) in the Categories table =

Count ( Teams::CategoryID )

[scchoi]

I'm still struggling to get it to work. I'll show you a screen shot of what I'm talking about. In my example, its slightly different in the category (I just use a sport analogy cause It's easier to understand I guess?)

Just replace:

Category with Type

Team with Stamps

and Home still = Home

I've posted my relationship, layout, and fields that I feel applies to my question.

look at the second screen shot, and see how my bottom left portal displays the type name but not the number of stamps in each type?

in other words, it should reflect the Chart's number in correspondence to the the number of stamps per type.

[comment]

I am a little confused by your relationships - I don't see why you need so many of them.

Anyway, try changing your Type Count field formula to =

Count ( TYP_STAP_Stamp::kf_Type )

[scchoi]

The relationships are just there for my note, since this is my first database that I'm building from scratch. A lot of the things are tests and trial. Which ones do you have question about?

I'm still coming off as a blank I'd have attached ac ouple more screen shot for you to get a better idea of where I'm getting at.

The first one is the detail of the stamps. Bottom right of it is where the Type is indicated per stamp (under _kf_Type)

The second one is a snapshot of of my home table, where it might be confusing to you. I needed to create a two calculation fields for the TOTAL NUMBER OF RECORDS (STAMPS) and the TOTAL VALUE OF STAMPS so I used the a relation to accomplish this. Any other methods I tried prior to this proved futile.

My 3rd snapshot is a picture of how I did my graph. I used a script and looping technique to set the variables of x and y.... I don't know if I can somehow relate this to my function that I'm trying to make but I just put it there as reference anyways.

[comment]

Does this work for you?

CountChildren.fp7.zip

[scchoi]

Hi, it eventually worked for me! I think the problem was that I had an unrelated table... this is probably my fault for complicating things too much. But anyways, thanks a lot! Appreciate it.