A real brain challenge

[ShaunWillmott]

Friends, you have always managed to help before.

The problem: I'm trying to calculate the Occupancy rate for a Flat, people move in and out of a flat (of which there are 8) during the year and I need to work out the time (in wks) between moves (when Flat is unoccupied), I have a query by Flat which lists the tenants in order they moved and their departure date. I need to hold the # of empty weeks for each month. Currently I'm getting the 1st record and holding the depart date, then I get the next record and datediff the depart vs. the Start dates which gives me the # of weeks between the residents, but I need to hold this # of weeks for each month? so if res1 leaves on Aug 17th and Res2 moves in on Oct 8th, Mth8 = 1, Mth9 = 4 & Mth10 = 1 (6 wks empty)
Anybody help? (or understand what I'm on about)

Cheers, Shaun

 

[dakcg]

Hi, Shaun, and I do understand what "I'm on about". And I think I can help. I have an idea, anyway. You speak of having a query that you run, what I need to know is: What are you querying? You have a table with the information stored in it, right? If so, there is nothing that says that you cannot store the time an apt. is empty, in the same table. You could create another query that could append your table by one record that would contain (Here I am at a loss, because I don't know how your table is set up) anyway, you would have to create a code word that meant to you - vacant - and the query would append to your table the code word you created that would separate that line from others, and in the first date field you would write a query that would append to the field the current date (if that is the same day as the departure, if not this could it ugly!), anyway, append to the field the (current date + 1), that should get the next day to be appended, which would coincide with the first day the apt. was vacant. You then create a macro that will run the first query, adding whatever exit date info. Then, it would run the other which would put the first line of the vacant time in place. Then you do the reverse when it is occupied again.

Is that all clear to you?

If not, write back. I really think I can help you. I have put together a time card table to track Time In and Time Out for payroll purposes, and it works great. I think I can help you.

DAK


[This message has been edited by dakcg (edited 01-31-2002).]

 

[ShaunWillmott]

Thanks for the reply DAK,

I have two tables; Resident, which contains a residents details, including their startdate and departdate, also FlatID which has a relationship with the Flats (apartment) table which contains Flat address, phone, etc.

The query (FlatHist) Uses the Flat table and relationship with the Resident table sorted by Flat Address and Resident StartDate

Currently I have a module which gets the Flat Add and if it changes starts the loop again, then if it's the first record I hold the DepartDate and then for subsequent records (for that Flat) I datediff the departdate with the StartDate (Gives me the # wks vacant) what I need to do is assign each of these weeks to the correct Mth i.e. Resident moves out 15th Feb 02 and next resident moves in on 18th March 02, then there are 4 weeks vacant but 2 in Feb and 2 in Mar.

I have array (1 To 8, 1 to 12) to store the FlatID and # weeks Vacent for each month, but can't seem to get the code right?

Thanks again for you help.

Regards

Shaun

 

[ShaunWillmott]

DAK,

Looks like I have solved it, my code looks like:

StartDate = DepartDate
EndDate = .Fields("StartDate")
iWeek = 0

Do While StartDate <= EndDate

iWeek = iWeek + 1

If iWeek > 1 Then
iMth = GetFiscalMth(FiscalDay, FiscalMth, StartDate)
MissingWks(NumFlats, iMth) = MissingWks(NumFlats, iMth) + 1
End If

StartDate = DateAdd("ww", 1, StartDate)

Loop ' StartMth < EndMth

I have done a quick check of the results and they seem to be right, I have struggled with this for hours and never thought that the answer would be so simple (They always are!!!!)

Anyway once again thanks for your help.

regards

Shaun

 

[dakcg]

I must admit, that I do not understand the code you use, because I am a novice to code, but I am good at ideas, so I am glad I could help you. Maybe you could help me with some of the code problems I am having. Who knows.

DAK