Can’t add data to child table

Topic

New Reply

I’m a new database designer. Access 2013 came in my Office bundle, so I decided it was time to learn to make my own databases.
Having early success with simple projects, I decided to tackle one that I thought would be easy, but is frustrating me.

Here’s the scenarion:
Our homeowners association contracted to have an inventory of our trees done. A map was generated and each tree given a unique number. But, there is no correlation between the tree number and where to find the tree. Quotes for maintenance give the tree number but not a location. Hoping to end the difficult hunt on the map:

I created 3 tables.
1) ‘Owners’ contains things like Name, phone numbers, and email. Its primary key is OwnersID. It also has a field, PropertyID which is the same PropertyID from the Properties Table
2) ‘Properties’ containing the Primary Key PropertyID, street number, and a few other fields that directly relate to the individual property.
3) ‘Trees’ containing a Primary Key TreeID, the tree number from the survey, some fields that directly relate to the indivual tree. It also includes PropertyID

Properties is the Parent table with a one to many relationship to PropertyID in the 2 child tables.

The Owners and Properties tables are fully populated.
The Trees table is populated except for PropertyID and location on the property (a dropdown choice)

What I want to do is choose a street number from the Properties table, assign an existing tree number from the survey and add values to other fields in the Trees table. But I can’t do that without a value in the PropertyID. The street number is unique to the PropertyID.

Reply | Quote

Replies

You’ve bumped into the hard part of database design, and that is getting a sound table design in place. One question I have is whether a street number is unique across all of the Properties. For example, where I live there may well be several properties that have a number of 2345, but have a different street name. If you mean the inclusion of the name as a part of the street number then that should make it unique. If that’s the case, then I would use a subform that displays trees on a given property and new records added in the subform will get the property ID directly from the main form that displays the details of a property. To display the property you are after, you could use a combo box to select the property you want. There are wizards available that will do at least some of the work for you.

Reply | Quote

You’ve bumped into the hard part of database design, and that is getting a sound table design in place. One question I have is whether a street number is unique across all of the Properties. For example, where I live there may well be several properties that have a number of 2345, but have a different street name. If you mean the inclusion of the name as a part of the street number then that should make it unique. If that’s the case, then I would use a subform that displays trees on a given property and new records added in the subform will get the property ID directly from the main form that displays the details of a property. To display the property you are after, you could use a combo box to select the property you want. There are wizards available that will do at least some of the work for you.

Thanks Wendel,
I think you pointed me in the right direction with a subform and combo box.

To answer your question, yes, there are 56 unique address all with the same street name.

Reply | Quote

Hi Don,
I see that you treat the concept Property as the parent of the concepts Owner and Tree. But severing an owner from a property does not necessarily make the owner vanish. For example, an owner might have two or more properties. Similarly, a tree might have existed before its lot were developed, and continue to exist even if a property line were to be redrawn.

Your database design could reflect the above if instead of having PropertyID embedded in Tree and Owner, there were separate tables depicting these relationships: Tree-Property (which would at minimum contain TreeID and PropertyID) and Owner-Property (which would at minimum contain OwnerID and PropertyID).

This design might also make it easier to enter data about a particular tree or owner without having to establish its relationship to a property. For example, if a new family purchased a home but the closing date had yet to be reached, you could nonetheless enter the family’s information into Owner.

Hope this helps,
Dave

Reply | Quote

Thanks Dave,
The problem that I’m trying to solve is that looking at a map of the association to try to spot a specific tree number is too time consuming to be practical. The tree company refers to trees by their unique number, but we need to narrow the possibilities of where to find the tree.
Your idea of adding tables is one I thought of but didn’t know how to proceed. I’m off to learn more about different relationships. I see I need to understand those better.

Reply | Quote