Do I Use a Snapshot?

[brc]

I have a property management company I am doing an application for. I am trying to figure out how to handle the leases. In most cases the lease is for a set amount of time and no changes are made so that works fine. What I am trying to do is have a lease that has multiple tenants and one of the tenants leaves but be able to reflect that change with the current Lease. So say the lease has 1 year starting Jan 1,2011 and 1 tenant leaves in June. I would like the reports to still reflect that the first six months that this tenant was still there. right now when I make the change it affects the current lease and then When I print report for any of the 1st six months that tenant isn't there. Can anybody shed any kind of light on how to manage this.

Thanks

[ajetrumpet]

.................

[ajetrumpet]

right now when I make the change it affects the current lease and then When I print report for any of the 1st six months that tenant isn't there.

OK...how about a sample, so pics and/or files can be compared to words??

[brc]

OK I have a table called Lease which holds all the information of current and archived leases. Structure is [LeaseID, PropertyID, MoveInDate, StartDate, EndDate, (other info)]. When I create a Lease one of the options is to specify the tenant(s). The tenants are display in a multi select listbox with a Row Source Coming from the Tenants table. On the After Update of that list box i have a bit of code that checks all the the names selected and unselected and either adds or deletes from a junction table called LeaseTenants. Then another bit of code writes the resulting info into a table here is the code that writes to the table.

 Public Function TenantsPerAddressTable() As Boolean
    On Error Resume Next

        Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
        Dim strColumn1 As String, strColumn2 As String
        
        Set db = CurrentDb
        
        sSQL = "SELECT LeaseTenants.LeaseID,[Tenant Name] " _
        & "FROM [Tenants Extended] RIGHT JOIN (LeaseTenants RIGHT JOIN [Active Leases] ON LeaseTenants.LeaseID = [Active Leases].LeaseID) ON [Tenants Extended].TenantID = LeaseTenants.TenantID " _
        & "ORDER BY [Active Leases].LeaseID, [Tenants Extended].[Tenant Name]"
        
        Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
        
        If Not rst.BOF And Not rst.EOF Then
          rst.MoveFirst
          strColumn1 = rst!LeaseID
          strColumn2 = rst![Tenant Name]
          
          rst.MoveNext
          Do Until rst.EOF
            If strColumn1 = rst!LeaseID Then
              strColumn2 = strColumn2 & ", " & rst![Tenant Name]
            Else
              'check if this property is already listed
              If IsNull(DLookup("[LeaseID]", "[TenantsPerAddress]", "LeaseID = " & strColumn1 & "")) Then
                sSQL = "INSERT INTO TenantsPerAddress (LeaseID, Tenants)  " _
                     & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
              Else
                sSQL = "UPDATE TenantsPerAddress SET Tenants ='" & strColumn2 & "' WHERE LeaseID = " & strColumn1
              End If
                db.Execute sSQL
                strColumn1 = rst!LeaseID
                strColumn2 = rst![Tenant Name]
            End If
            rst.MoveNext
          Loop
          
          ' Insert Last Record
          'check if this property is already listed
              If IsNull(DLookup("[LeaseID]", "[TenantsPerAddress]", "LeaseID = " & strColumn1 & "")) Then
                sSQL = "INSERT INTO TenantsPerAddress (LeaseID, Tenants)  " _
                     & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
              Else
                sSQL = "UPDATE TenantsPerAddress SET Tenants ='" & strColumn2 & "' WHERE LeaseID = " & strColumn1
              End If
              db.Execute sSQL
        End If
        
        Set rst = Nothing
        Set db = Nothing
    End Function

Problem is, is that the way this is done, if i remove a tenant on a multiple tenant property this change effects the whole lease not just from that time forward. I need to still be able to maintain the the history, that that person actually stayed there for the first however many months.

I don't have alot of DB experience. This is my first major Access DB project. Most of my experience either comes from post secondary and small web projects using MySQL.

Hope that helps

[ajetrumpet]

Problem is, is that the way this is done, if i remove a tenant on a multiple tenant property this change effects the whole lease not just from that time forward. I need to still be able to maintain the the history, that that person actually stayed there for the first however many months.

lets take this portion first. could you keep another table in there with unexpected and/or early vacancies?? If you do that, you could relate the table to any of the others, and simply use that join to display this unique info when needed on a form somewhere. Or look it up. You can do that too.

I did not go through your code or the rest of the post. This first thought came up, and I was almost certain that was the best way to do it. Your thoughts?

[ajetrumpet]

just on first glance of code, I'm also assuming that if you delete a tenant, the way you're doing it is simply selecting the name from a listbox and running that, or similar code, one more time for the deletion process??

is that part of the issue as well?

[brc]

So maybe having to basically close this lease. and have them create another with the new tenant?

I didn't want to go this way because The actual paper work they wouldn't have to cause they just make an amendment to that lease. If they have to archive and open a new lease might be confusing for them

[brc]

just on first glance of code, I'm also assuming that if you delete a tenant, the way you're doing it is simply selecting the name from a listbox and running that, or similar code, one more time for the deletion process??

is that part of the issue as well?

Yes this could be part of the problem! This whole thing has got me so confused. I don't want them to have to create a whole new lease each time it might be changed and I don't want to lose the history if they do change some info. There is got to be somebody out there that can help me.

Please and thank you!
brc

[ajetrumpet]

brc,

can you upload the db so I can replicate this event that's actually happening to you??

please follow these instructions when guiding us with the file as well.

[brc]

Hello ajetrumpet,

Even with a condensed version of the DB it is 11MB and this forum has a 500K limit for DB's. Is there another way I can get it to you?

[ajetrumpet]

is condensed version a "compacted" version?? compact it first to get rid of all the temporary junk.

the max for ZIP files is 2 mb. And I can zip a 20 mb database file down to 2 mb. that should get you there. ZIP it.

[brc]

Condensed meant that I wasn't giving you the whole thing just the stuff that was applicable to what I am trying to get at. I compacted and zipped it and have attached it.

OK, so for as an explanation to what you will be looking for.

I have it opening to Leases right off the start for you. If you notice on this form you will see all the information regarding the lease plus a listbox with a list of tenants. The list is all the tenants with the ones that are applicable to that lease are highlighted (Selected).

Making a change will update the LeaseTenants Table (junction) and the TenantsPerAddress

Each month they print a report for each property to send to the owner (their Client) As we go along the way I have it setup it will work that way. But say if I change something on the lease in June then go print the report for March the Data that was changed will now exist in March because the report is being drawn from the current data for the lease.

Have a look and let me know if you have any more questions.

All I can think is that every time there is a change or once every month a snapshot is taken and then the reports are pulled from the respective snapshots. Would that make sense?

[ajetrumpet]

I can't answer that because I can't test this. what report are they running?? I can obviously fix the issue, but I really do need a report in access that's ultimately used by your clients to make sure it works before I post the solution back here.

is this a third party reporting system?

[brc]

Sorry I didn't think you needed the actual report.

I have attached a copy of the DB with a report you can look at. When you open the DB it will open straight to Reports Dialog. Select Rent Roll by Owner, Under the Filter select Bob Jones, then select the month of March.

Right now the report displays the properties owned by Bob Jones but is showing not just the current active leases but all the leases. That is why you will see two properties from the same address.

Got questions please let me know. I have probably made some rudimentary mistakes but I'm willing to learn.

Thanks so much for the help

[ajetrumpet]

now I need the ACTUAL reports that show what the problem is.

the report you gave me was the one that opens the other ones that are the actual problem objects. So I still don't have the relevant reports.

What I'd also like you to do is give me some step-by-steps that will give me an opportunity to replicate that issue of "deleting a tenant and needing historical info on that tenant".

if you can do that for me, we can move forward and start working on a solution to this.