"Bang my Head" Issue

[sgladie@bgsu.edu]

Access 2013


I am trying to create a query that will provide the turn over status of a unit. There are four statuses a unit can be in: Occupied, Ready to Rent, In Renovation, Out of Service. My setup query's data is:


7249 Occupied 9/1/2015 8/19/2015 13
7249 Ready to Rent 8/19/2015 7/14/2015 36
7249 In Renovation 7/14/2015 1/9/2015 186
7249 Occupied 1/9/2015 1/9/2015 0


UnitID Status StatusDate PriorDate TotalDays (respectively)


We need for it to pick up the days that it was in Ready to Rent (13) and In Renovation (36). This is just one scenario. A unit can also just go from Occupied > Ready to Rent > Occupied, or Occupied > Out of Service > In Renovation > Ready to Rent > Occupied. But we only want the Ready to Rent and the Renovation days. For some reason I just cannot figure out how to pull just those days.


Any suggestions or help is much appreciated! I am hoping I am making this harder then it really is - over thinking it, maybe! Thank you!

 

[plog]

My guess is you are overthinking it or underexplaining it, because from what posted, this SQL will give you what you want:

SELECT * FROM YourQueryName
WHERE Status="In Renovation" OR Status="Ready to Rent";

If that's not it. Provide more sample data, 2 sets in fact:

A. Starting sample data. Like what you posted above (but with actually query name)

B. Expected results. What you expect your final query to produce based on the data in A.

 

[Minty]

Just sum the total days where the status <> Occupied ?

 

[sgladie@bgsu.edu]

Minty - if I do the <> Occupied, it will remove the calculation I need - the TotalDays value 13.






Plog - same thing goes if I just ask for In Renovation and/or Ready to Rent, then I will receive the In Renovation TotalDays value 186 - which is really the days it was in Occupied status. I only need the days it was In Renovation or Ready to Rent, but the TotalDays is calculating the value from the prior status, so it is not that simple to just set the parameter to those two status. The outcome is wanting it to have a total turnover days of 49 - (13 + 36). I hope this helps with the confusion - providing more information. THanks!

 

[plog]

So why did you build that query if its not giving you what you want? I'm assuming (and hoping) that the PriorDate field is a calculated value in your query. Why? Seems like a NextDate would serve your purpose. Then just do your TotalDays calculation off the Next Date.

 

[sgladie@bgsu.edu]

After I replied to both of you, I sat back and thought more about this. And yes, you are right, I need to use the other calculation I created for TotalTurns. I am sorry if I wasted anyone's time. THank you!