This topic is 4363 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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!

##### Share on other sites

Anybody have any ideas on this one? Thanks.

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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).

##### Share on other sites

This topic is 4363 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account