Change Last Name in Tenant Table

[mcdonaldl]

have "Rent Program", how do I handle "Tenant Table" to change tenant last name to married name and not change existing records (new rent receipts to have married name, old rent records to have single name).

[CJ_London]

sounds like your db is not structured to cater for this situation. But not enough information to suggest what you would need to do other than to include a date field in your tenant table. Or perhaps it needs to be in the one to many table (one property, many tenants)

[mcdonaldl]

my Tenant Table has TenantID, FirstName, LastName, etc with a 1 to many to TenantReceiptInfoTable. My TenantInfo Table has MoveIn, MoveOut date fields. Would married last name be a new TenantID?

[ArviLaanemets]

You need tables:
tblTenant: TenantID, ..., [CurrentForename], [CurentLastName] ( [CurrentForename] and [CurentLastName] are optional calculated fields meant for making selecting person with combo on forms with current info at data entry time easier);
tblTenantName: TenantNameID, TenantID, ForeName, LastName, ValidFrom (ValidFrom is a date tenant has this name from).

You need an UDF, which returns a valid forename/lastname for tenant at given date (finds a record with Max ValidFrom for given TenantID where ValidFrom <= given date, and returns Forename/Lastname from this record).
Now you can get proper name for any tenant at any time moment.

[mcdonaldl]

Thanks ArviLaanemets for reply, but, your explanation is way above my NOVICE status. Is there an easier way?

[Welshgasman]

Thanks ArviLaanemets for reply, but, your explanation is way above my NOVICE status. Is there an easier way?

Just make them a new tenant?

[Micron]

Why would anyone care if the tenant legally changed their name? They are the same person regardless of what you call them so their history goes with them if you follow the standard practice. This sort of thing is often cited as a reason for using an autonumber ID field to uniquely identify a record (in this case, a record about a person) is it not? If you have to change a field value, the related records remain just that - related.

I don't see an issue here. Who cares if they change their name? The are the same exemplary tenant, deadbeat, or something in between. Perhaps there is a valid point and I missed it.

[Welshgasman]

@Micron,
I believe it is due to the receipts issue, mentioned in original post.
If they change the name any receipts reprinted will have the new name, not the old.?

[mcdonaldl]

Thanks Welshgasman and Micron for comments - As Landlord I do care if a single Tenant changes their last name as all records are in single name, Rental Agreement, etc. To me they are a different person, and new rent receipts in new last name will change old rent receipts from single name to new last name. A new TenantID won't give me history of their previous TenantID. So I am back to my original post

[Welshgasman]

To me they are a different person,

So make them a new tenant.?
Add a field as to previous tenantID for your history.

Either that, or change your receipt process to store name/id at that time.? like an order detail and price normally does.?

In your position now, I'd go with new tenant and new field?

How often is it going to happen.?

Miss Jones, becomes Mrs Jones-Davies, then gets divorced and goes back to Ms Jones, then remarries as Mrs Jones-Williams.
I am sure you would be able to get at the history, if you follow the chain of previous tenant.?

Good luck anyway, as I am out of ideas.

[mcdonaldl]

new tenant and new field worked! My thanks to Ajax, Arvilaanemets, Welshgasman, and Micron who got me thinking in the right direction - I appreciate you taking the time to replyand sharing your knowledge...