Do I Use a Snapshot?

[brc]

OK So I have attached some photos of reports. Jan_Before is how the report would typically look when printed in January. If a tenant was changed in April it should reflect on the reports from then on. But if you look at the image Jan_After you will see that now there is two tenants for Jan. So what should happen is that the Jan - March reports should have just one tenant and from then till the end of the lease should have two tenants.

[ajetrumpet]

OK, but can you still include them in the db?? pictures are good, but I can't test a solution just by looking at pictures.

solutions to problems can always be wrong if you don't have the right tools to work with.

[brc]

Ok this is what I did.

1. Open up Report Dialog and select Rent Roll by Owner, Filter by Bob Jones, Selected January 2011. It displays all the rent info including the Tenants in the Property. Take note the tenants.

2. Open Lease Details, change current tenants by selecting or unselecting the tenants for that lease. Do this for the first lease that comes up because it is for a property that Bob Jones Owns. Note that this change we are making is in April.

3. Now go back to Report Dialog and select the exact same report you did before. As you will see the tenants has changed.

What I would like to happen is that the report still show what tenant was there in January not to reflect the change we just made in April.

Does that make sense?

[ajetrumpet]

hey brc,

I completely forgot about this thread. Have you solved this already or are you still looking for my assistance?? sorry!

if you still need help, I'll get back to you with a solution when I hear a reply.

[brc]

Not a Problem!

Yes I am still looking for a solution. So any assistance would be great.

Appreciate anything you can do.

Thanks

[ajetrumpet]

I will get back to you tonight bud. I'm a little busy right now. Chances are that I won't find a solution is extremely unlikely.

that's what I do for a living...

[brc]

Awesome, Like I said appreciate anything you can do. This has got me confused and I am sure your solution will help me with this problem and help further down in this project.

Thanks So much!

[ajetrumpet]

Ok this is what I did.

1. Open up Report Dialog and select Rent Roll by Owner, Filter by Bob Jones, Selected January 2011. It displays all the rent info including the Tenants in the Property. Take note the tenants.

2. Open Lease Details, change current tenants by selecting or unselecting the tenants for that lease. Do this for the first lease that comes up because it is for a property that Bob Jones Owns. Note that this change we are making is in April.

3. Now go back to Report Dialog and select the exact same report you did before. As you will see the tenants has changed.

What I would like to happen is that the report still show what tenant was there in January not to reflect the change we just made in April.

Does that make sense?

OK bud, here's what's happening:

after you make new selections in the listbox, a procedure runs to update whatever table is necessary. I'm sure you know that. So the problem lies right there. When that procedure runs, the data that you want to archive is gone. So that's the issue. The report has nothing to do with it.

So what you need is some sort of interface design that is going to allow users to "archive" members that have lived at that address. So for instance, if a new tenant moved in, you need to offer users a way to archive the old name - the person that moved out. That's the first thing to do. Then after that you can focus on joining this archive table as a "child" table, into the report SQL, if necessary. However, it would make more sense to use a "sub report" on the main report that you're using now, in order to show details about previous occupants.

does that make sense?

Due to the db because large to begin with, I am not going to do the work for you without pay, but I can "guide" you into how to do it if you're going to do it yourself anyway.

Also note that I didn't look at any code or do much analysis. That's not necessary, because the data trail is really all I needed here to explain what the issue was.

[brc]

Ok that would make sense. That requires that the user Manually archive the tenant. Which probably isn't a problem but how about something where when the change is made it is logged in a table when the change was made. The report then checks this table if there was any updates to the lease in question.

Do you think this would work or is a good way to approach it?

[ajetrumpet]

how about something where when the change is made it is logged in a table when the change was made. The report then checks this table if there was any updates to the lease in question.

that's fine, but why would you offer "update" information in a report?? I don't know much about the leasing business, but I'm pretty sure that either the owners of these properties or the management company that rents them out (or both) don't care about database updates. They probably care about why the updates were made. But I would think more or less, that they would care about the sublease person's credentials and personal information, no??

Your way is fine, but it is, in general, more complicated than what I suggested. For one reason - that method would be using your "updates" table for more than one purpose. That's actually a violation of normalization rules, theoretically speaking, but I don't really care about that. I only mention it because that right there tells you that you might want to consider it as a secondary initiative.

and don't forget too, that as of now, someone could update any of your leases on accident with this listbox. If that happened more than once and you implemented this report method you're thinking about, your users would see a report and subreport that information about 2, 3 or even more changes that apparently happened when they absolutely mean nothing. Not to mention that with accidents, nothing really happens in real life even though they're forced to think something happened.

[ajetrumpet]

sorry brc,

I forgot to mention what my approach would be. I'm sure you don't like reading a bunch of complaints.

At any rate, I would first put a safety net on that form so the listbox doesn't change unless requested to change. You can even lock it down.

The next thing I would do then is figure out what needs to be in the "update" table. That'll dictate then, how complicated your procedure needs to be after someone updates the lease information. And it will also dictate whether any code is needed behind or associated with the report, as it will be needing some information, if any, from this new table as well as what it has now.