Hello, I work in a charity who help people in need with temp accommodation (houses or units).
I need to add vacant periods for each property to calculate the vacancy rate. The yellow rows in my sample are the ones to be added by vb in a table called Lease.
I have a sub-form who show periods in Lease for that property.
We want the VACANT records to be display too.
Thanks millions in advance - I been going crazy about this for days.
Thanks Colo 7 2146 zmbd 5,501
Expert Mod 4TB
In your first image I see what looks like an excel/workbook file - is that correct, and now you are trying to do the exactly the same thing in Access that you were doing in the worksheet?
You can do this by expansion query and a few other means; however, if I may:
+ One doesn't normally store a calculated value such a s [RentLength] in the database.
Instead, this is calculated "on the fly" in the query using a calculated field, or a similar concept with a calculated control on a form or report.
+ I notice that your start and end dates for the rental period appear to coincide with the start end dates of the vacant periods so to take your first few rows:
rented from 2012-01-11 thru 2013-11-28
vacant from 2013-11-28 thru 2014-06-30
rented from 2014-06-30 thru 2015-02-23
Thus, you should have all of the information needed to calculate the vacancy directly:
So what I would do is take your earliest rent start date of the period in question (2012-01-11) and the oldest rent end date (or current date) calculate the total days between the two. (you don't have one in the first image so today's date 2015-12-21 which gives 1440 days) sum the calculated rent length from records
Note there appears to be something off in the first image:
2012-01-11 thru 2013-11-28 = 687days
2014-06-30 thru 2014-10-02 = 97days
2015-02-23 thru 2015-07-27 = 519days
2015-11-15 thru today = 36days
Sum is 1339
(1339/1440)*100 = 92.99% occupancy == 7.01% vacancy
This can also be done in the worksheet.
Your result set doesn't make sense.
Why does the second vacancy overlap with an existing lease? And what algorithm do you use to determine that a lease doesn't count towards occupancy?
Also, why does your vacancy range overlap with the end of the previous lease and start of the next lease? If seems to me that you run the risk of having 2 groups of people in the same lease on the same day unless you include some sort of time element.
zmbd 5,501
Expert Mod 4TB
Good catch Rabbit, I didn't take that close of a look at the second data-set shown - instead focusing on the first... I have a feeling that there are other issues here yet to be discovered :)
Thanks for the quick response and help. I try to do this in Access (i copy my query result into Excel only to make a sample). I need to add those VACANT records into the Lease table. I highlight in yellow the records to be added. Rabbit thanks for pointed the overlapping in the dates.
Thanks guys
- Public Function FillVacancy(PropertyID As String, TenantID As String, DATEEFF As Long)
-
-
Dim dbs As Database
-
Dim rst As Recordset
-
Dim strSelect As String
-
-
strSelect = "SELECT * FROM Lease WHERE [PropertyID] = " & "'" & PropertyID & "'"
-
-
Dim datMin As Date
-
Dim datMax As Date
-
Dim datLog As Date
-
Dim datNew As Date
-
Dim datOld As Date
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset(strSelect)
-
With rst
-
' Find first and last logging date.
-
.MoveLast
-
If Not IsNull(!EndDate.Value) Then
-
datMax = !EndDate.Value
-
Else
-
datMax = Now
-
End If
-
.MoveFirst
-
datMin = !StartDate.Value
-
' Set initial low value of empty interval.
-
datOld = datMin
-
' Set initial high value of current empty interval.
-
datLog = datMin
-
.MoveNext
-
' Locate empty intervals.
-
' Stop when reaching the last of the old records.
-
While .EOF = False And [PropertyID] = PropertyID And datLog < datMax
-
' Set high value of a possible empty interval.
-
datLog = !StartDate.Value
-
' Increment low value of possible empty interval by one hour.
-
datNew = DateAdd("h", 1, datOld)
-
' Fill empty intervals with hourly recordings of Null values.
-
Do While DateDiff("h", datNew, datLog) >= 1 And datNew < datMax
-
.AddNew
-
!PropertyID.Value = PropertyID
-
!LeaseID.Value = "VACANT"
-
!TenantID.Value = TenantID
-
!DATEEFF = DATEEFF
-
!StartDate.Value = datNew
-
.Update
-
-
datNew = DateAdd("h", 1, datNew)
-
-
Loop
-
' Set low value of the next possible empty interval.
-
datOld = datLog
-
.MoveNext
-
Wend
-
.Close
-
End With
-
-
Set rst = Nothing
-
Set dbs = Nothing
-
-
End Function
I starting with this code but it got too confused and I don't think I am in the right path. too messy
Please use code tags when posting code or formatted data.
There's no need for any code, you can do it in just SQL.
What you do is join the table to itself on the leases that occur after the end of the lease.
The end date plus one day from the first reference becomes the start date of the vacancy.
You take the minimum date of the start date from the second table and subtract a day as the end of the vacancy.
And in the having clause, you filter out those that start right after each other.
Also, I agree with zmdb, you should just keep the results as a query. It's a bad idea to store it in the table.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dennis M. Marks |
last post by:
What is the maximum valid date range for the date(yyyy,mm,dd) function.
--
Dennis M. Marks
http://www.dcs-chico.com/~denmarks/
Replace domain.invalid with dcsi.net
-----= Posted via...
|
by: PromisedOyster |
last post by:
Is there a way that I can get a resultset that contains unique dates in
a given date range without the need to have a temporary table and a
cursor?
perhaps something like:
declare @start_date...
|
by: Mark |
last post by:
Hi
I have been trying to convert the week number to a range of dates that
I can use. It should be fairly simple for you guru's out there but for
us mere mortals it is beyond our grasp.
I know...
|
by: Adam |
last post by:
Hi All,
This may be a really obvious thing that I'm missing ... but if anyone can
help, I'd appreciate it.
I have MS Access 2000:
I'm using it for a CRM type database. I have a table with...
|
by: Sara |
last post by:
I have followed instructions on the http://allenbrowne.com/tips.html
for limiting a report to a date range. At the bottom there is a note
that says
You will end up using this form for all sorts...
|
by: RoadRunner |
last post by:
Hi, I have a employee vacation database that has a vacation table that
has the employee name, pay week and date of vacation. I have another
lookup table with pay week code and date range for the...
|
by: rugger81 |
last post by:
I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a need to highlight a date range in a bar chart. If the
"highlighting" is accomplished by changing the bar column color for the
specific months in the date range, that would be great.
...
|
by: Deano |
last post by:
The short version;
In short, given one date range (start and end dates) how can I find the
period that overlaps with another date range?
The long version;
I have knocked up a little application...
|
by: Jerry Maiapu |
last post by:
I HAVE THIS Sql Query ( qrysummary) :
SELECT .Salutation,
.,
.,
IIf((WorkingDays(!!,
!!))-()<0,0,
(WorkingDays(!!,!!))-()) AS ,
IIf(IsNull(),0,) AS ,
.SumOfHrtMintNoAOT,
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |