Change of Ownership
I have written a database which manages my dog breeding activities, I am not great with databases - it was never my day job - and I have have hit a snag with which I would be grateful for some help.
I have a three tables;
One contains all the dog's (puppy's) details that I need (Dog's Unique ID No., DoB, Sex, `Sire, Dam, etc...).
One contains all the Owner's details ( Owner's Unique ID No., Name, Address, Phone Nos, Emails address, etc...
One Join Table which uses the Dog's Unique ID No. and the Owner Unique ID No. as foreign keys and links the two tables together using a DogOwner Unique ID No.
This works fine when I sell a puppy or dog and I have no problem with assigning more than one dog to an owner (some owners have several of my dogs) but...
Sometimes a new home doesn't work out and I buy the puppy or dog back and resell it to different owner.
What I can't work out is how I can keep records of all the owners (active or not) whilst transferring a dog from one to the other, I don't want to delete an owner as I need to keep a record who has owned my dogs in the past but I also need to produce a file of information for the new owner using layouts for Insurance Cover Notes, Microchipping, Registrations etc.
i have tried deleting the DogOwner Unique ID No. from the record (which I thought was the only link between dog and owner) but somehow all layouts still display the original owner.
I just can't get the database to recognise the new owners - the old owner's data is displayed on every layout - including the new Agreement of Sale which I need to send to the new owner.
It is rather like a serial numbered product being returned and resold so there must be a way of doing it.
I have just installed (yesterday) FMP 16 - upgrading from FMP 12, running on a Mac.
Sorry if I haven't explained this very well.