Using a form to change the records involved in a one-to-many relationship

[Seabird_databases]

Hi all

First time using any kind of forum to solve the frequent problems I encounter learning how to use different programs (QGIS, R etc), please feel free to correct or give me pointers on how to use the forum better. My background is in ornithological research, not coding or database creation but I am willing have a go at learning anything and have dabbled with VBA code. I apologise there may well be something on the forum which relates to my question but I have struggled to find the correct phrasing even for the title of this post.

I have a simple Access database to keep track of the cats on our island, of which there are roughly 34. Due to the relational nature of the data an Excel spreadsheet was causing some problems with users and the data kept was not well standardised. Two main tables, tbl_owners and tbl_cats with a one to many relationship, eg one owner can have many cats. I have given up on a many to many relationship with a junction table (cats can have many owners during their time on the island) because I couldn't get a form to function the way I needed it to, so I simplified the problem in the hope I could manage the challenge better. I have created a form which shows the owner's information along with the cat's using the one to many relationship but what I am struggling with is how to change the ownership of a cat. The records displayed on the form are selected by the owner's name (main form) to display the cat/s they look after (subform). I could simply write over the previous owner's records however it would be useful to know where the cat lived before in case it goes missing (I can use a notes column to keep track of what owner had which cat in the past) and I would prefer that on this form the owners be selected using a combo box to avoid duplicate owners. New owners can be created on a separate form where it is easy to check that they don't already exist. I have a feeling that what I want to achieve is in fact blindly simple, unfortunately I am still stuck.



Any pointers in the right direction would be greatly appreciated, happy to post a copy of the database with some made up data if it helps. If anyone feels the urge to yell at my stupidity please try to remember that we all have to start somewhere and I have 2000MB a month internet at home so the internet is not an unlimited resource for me to learn how to be a database wizard

Many thanks

[ranman256]

I think youd want Owners-->Cats-->CatOwnerHist
1 owner owns many cats
but 1 cat can have a history of many owners, so youd want to keep a history of the owners the cat had.

tCatHist
--------
CatID
OwnID
Date

[Seabird_databases]

I think youd want Owners-->Cats-->CatOwnerHist
1 owner owns many cats
but 1 cat can have a history of many owners, so youd want to keep a history of the owners the cat had.

tCatHist
--------
CatID
OwnID
Date

Hi Ranman

Yes but this does not simplify or solve the main problem of how to create a form to change the owner of a cat. In fact it possibly adds more complexity (although I like the idea) as I would then need to work out how to get information from the Owners table into the CatOwnerHist table. Any ideas on how to structure the forms?

Many thanks

[CJ_London]

try something like this

tblOwners
OwnerPK - autonumber
OwnerName - text
...
...

tblCats
CatPK - autonumber
CatName - text
CatDOB - date/time
CatDOD - date/time
...
...

tblOwnership (linking table)
OwnershipPK - autonumber
OwnerFK long - link to tblOwners
CatFK long - link to tblCats
DateFrom - date/time

Then if you are tracking via owner

mainform recordsource tblOwners
continuous subform recordsource tblOwnership (linkchild=OwnerFK, linkmaster=OwnerPK)
on subform just two controls CatFK (a combobox to select cat) and DateFrom to set a date ownership from

and if tracking by cat

mainform recordsource tblCates
continuous subform recordsource tblOwnership (linkchild=CatFK, linkmaster=CatPK)
on subform just two controls OwnerFK (a combobox to select Owner) and DateFrom to set a date ownership from

This will show an owner at any one time (current owner is the one with the latest DateFrom value). If you want multiple owners per cat at any time then you will need a DateTo field as well - you'll also need some code to prevent an owner owning a cat prior to it's DOB