How to keep old records of previous occupants in a reservations type database?

[tony30]

Hi
Thank you for all the help received!
I would like to ask: how can I keep old records, of previous tenants or occupants that generated transaccions(bookings) and no longer are
in the room/facilities? once I have another new tenant, all the information from previous tenant AND the transactions will be gone.
Any ideas?
Thanks


Tony

[RayMilhon]

Depends on the structure of your database, but should be simple enough to fix. If not already there include the check in and check out dates. That way you can see who was there and when.

[tony30]

Sorry, I think I did not explain myself clearly, let's say you have a property that you rent to tenants, over the years some will be gone and will be replaced by others, If I wanted to know who was ten years ago, the tenant record will not exist anymore, because has been replaced by the new person. Probably I would need some sort of archive, however, to archive records from diferent tables (tenants, payments) will be difficult,.. sort of using append and delete, but how to do it for tables that are related?
Thanks.
Tony

[RayMilhon]

Same way You may want to change the idea of check in and check out dates to lease start and lease end dates but the principle is the same.

[RayMilhon]

Where you add the fields depends on the structure of your database.

[tony30]

Let's say I have tenant Robert leving today, so the checkout will be 11/9/12 ... and robert stayed in propertyA for 6 months . If I use checkIn Today for New tenant Adam, and I just update the previous tenant information (Robert) with the new Tenat Information ADAM, I will loose Robert information. Also, if the property is empty for months I will have to delete Robert, because no one is living there, and the records would be lost. Is a situation where people keeps changing and the only record that remain is the property.

[RayMilhon]

Based on your statement you're updating an existing record when a new tenant moves in when you should be creating a new record. I'm wondering if your data structure is correct. You should never delete a record if you are using the checkin and checkout dates approprately the information including vacancies would be easily obtainable through queries.

Using your example you should have 2 Records
1 for Robert with his checkin and checkout dates
1 for Adam with his checkin dates.

[tony30]

Parking V13 to experiment.zip
OK, this is what is happening(the real thing) : There is a table with residents in an apartment building, a booking table and a garage spots table, the residents can book a garage space and be charged to the accounts respectively, the problem arises when a resident who has booked in the past a parking spaces (for example . apartment 200 resident name Robert, with checkin and checkout dates and billed accordingly)leave the apartment building (move out) , and after several months Robert decides to move out. If I create another record for the new resident (ADAM) and keep Robert record the table will be too long, because this is a building for more than 250 Apartments and people keep moving in and out. I need a way to archive people who has moved out. I am posting the database if you want to take a look.
Thanks again

[RayMilhon]

First I would like to point out a couple of things in the tblResident you have 3 phone numbers using the format "!\(999") "000\-0000;0;_ " which is 10 numbers why are the field sizes set to 255 characters that takes up 715 bytes per record of which 30 max will ever be used. Same with e-mail address I would expect you could reduce that from 255 to 50

You currently take 1,538 bytes per record for each resident. You should be taking up 395 per Resident

Proposed Structure
tblResident
1. ResidentID Autonumber 4 bytes
2. Residentlastname text 30 bytes
3. ResidentFirstname text 29 bytes
4. ResidentMiddleInit text 1 Byte
5. HomePhone text 10 bytes - keep the existing field just change size to 10 bytes
6. Mobilephone text 10 bytes
7. Guestphone text 10 bytes
8. Email Address text 50 bytes - Could increase if required
9. Notes - here you have 3 choices 1 is a text field @ 255 bytes or a memo field can be used if 255 bytes is not enough. Another choice is to create a notes table with 3 fields residentid to link to this record, NoteID to be used as the Primary Key Note Date and Notes. This allows you to not only have plenty of room no matter how many notes for the resident but you can track them by date. You could also include a type field so you can choose if it's a resident note or booking note If you did this you would also have to include the bookingid as a foreign key.

the only thing i question in tblbooking is why is the spotID 64 characters that seems a bit excessive Assuming you have 2 spots for each apartment Thats 530 spots With digits included you should be able to handle that with 3-4 characters easily why 64?

Those changes will drastically reduce the size of your database. when required simply create a second database with the same fields and move the records you want to archive to the second database. You could do it annualy and add the year to the second database name