Jump to content

Help with Counting Records


This topic is 4781 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!

Link to comment
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.

Link to comment
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.

Link to comment
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).

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.