Help with Counting Records

[JTSmith]

Hi, I have a Table of records called "Jobs". These jobs have a field called "Status". Status has 4 entries:

1. Pending Move In

2. Renting

3. Pending Move Out

4. Moved out

I would like to have a ratio calculated that shows my "Pending Move Ins" minus my "Pending Move Outs".

So if I have 5 pending move ins, and 4 pending move outs, I'm +1. I need to know how to calculate this basically:

Count the Number of records whose status is "pending move in" - the count of records whose status is "pending move out".

I'm sure it's not a difficult process, but I'm struggling to figure it out. Thank you in advance for all the help!

[JTSmith]

Anybody have any ideas on this one? Thanks.

[comment]

You could define a calculation field (result is Number) =

Case ( 

Status = "Pending Move In" ; 1 ;

Status = "Pending Move Out" ; -1 

)

then use a summary field to total the calculation field.

[JTSmith]

You could define a calculation field (result is Number) =

Case ( 

Status = "Pending Move In" ; 1 ;

Status = "Pending Move Out" ; -1 

)

then use a summary field to total the calculation field.

Ok, thank you, that's very helpful. One question though, is there a way to have that without recalculating everytime I sort the records? When I click show all records, it tells me what I want, but when I sort by the status, it recalculates based on the current sort...

Thank you again.

[comment]

Do you mean "find" rather than "sort"?

[JTSmith]

Do you mean "find" rather than "sort"?

Yes, I have 4 buttons at the top of my database and they are Pending Move In, Renting, Pending Move Out, Moved Out.

That way if I'm focusing on all my current move ins, I can see those records, etc. If I show all records it works great. Is there a way I can have the formula calculate based on all records and not just the sorted ones? Thanks.

[comment]

To summarize all records in the table, regardless of the found set, you will need a relationship. This can be a self-join defined as:

Jobs::Status x Jobs 2::Status

(note the x relational operator). Then use a calculation field =

Sum ( Jobs 2::cStatus )

instead of the summary field (where cStatus is the calculation field described above).