JTSmith Posted February 15, 2011 Share Posted February 15, 2011 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 More sharing options...
JTSmith Posted February 22, 2011 Author Share Posted February 22, 2011 Anybody have any ideas on this one? Thanks. Link to comment Share on other sites More sharing options...
comment Posted February 22, 2011 Share Posted February 22, 2011 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. Link to comment Share on other sites More sharing options...
JTSmith Posted February 23, 2011 Author Share Posted February 23, 2011 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 More sharing options...
comment Posted February 23, 2011 Share Posted February 23, 2011 Do you mean "find" rather than "sort"? Link to comment Share on other sites More sharing options...
JTSmith Posted February 25, 2011 Author Share Posted February 25, 2011 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 More sharing options...
comment Posted February 25, 2011 Share Posted February 25, 2011 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now