I have been working on this database for a long time, and there have been several different versions of it. So far, my data is ultimately still in good shape, and I'm constantly adding new information, so it continues to grow, while I still don't have it all quite right. The tables all hold good, pertinent information, and seem pretty good on their own, but there are a few places where I have some weirdness like co-keys in one table, and no keys in some others. For the most part, these were all put together in ways that seemed to make sense to me at the time, but I've come to realize that there must be a better way in some situations.
I can summarize each table, but it would be best to just focus on the ones I know there are issues with first:
For example, in my table called DocInfo. I have the following fields: BookNum, PageNum, ReceptionNum, RecordingNum. I think a better way to do this is to make one field, BookAndPage, that is a combination of BookNum & PageNum, then I can delete those two fields. I believe this will make the database a little smaller, and make it easier to enter data and do searches on. Also, I created the RecordingNum field as a holder for the proper number to use when looking up a document. What I mean is every document has a ReceptionNum, but not all documents have a BookNum and PageNum. Unfortunately, the ones that do have a book & page are always referred to by their book and page rather than their ReceptionNum. So what I do in this case it tell it to put what I now call BookAndPage into the RecordingNum value, unless there is no BookAndPage value; in this case it will put the ReceptionNum into the RecordingNum value. Basically, this is to get one field that I can more easily search with one recording number. Can any of this be simplified, or can you verify that this will work alright?
And this is the biggest part of my question/the main reason for this posting: I have a table, ParcelInformation, that has relationships with three other tables, the relationships of which I'm unsure about. First of all, this table has a primary key of ParcelNum, which relates to the ParcelNum from the table ParcelLocationInfo. I can't make ParcelNum a primary key for this table, because this is the many side of a one to many, so I have no key. Should I make a new field ParcelID and make it an autonumber primary key, or is it just as good without?
Next, we relate ParcelInformation to AccountsByParcelNumber, which only has ParcelNum and AccountNum as co-keys. This is because each pairing only needs to occur once in the table as it helps to relate to the next table, OwnersByAccountNum. I could add other information relevant to just the AccountNum here, but I really don't have any. the AccountNum is really just a collection of owners and their interests, which I currently keep all in the OwnersByAccountNum table.
In the OwnersByAccountNum table, has OwnID and AccountNum as co-keys, but I'm starting to think that I could just make OwnID the key for this one. What I need to do, is for these three tables, is to ultimately have one updateable form that works with the current version of all this data. For example, if I pull up a certain parcel number, I should be able to get editable fields, tables, or subforms that will give me something like the following format:
[ParcelNum]
[AccountNum]
[OwnerID] [OwnerName] [Tenancy] [PercentInterest] [InAC] [ParcelNum]
Each indent shows the stepping into each table. I don't know how to go this far in on a form. Subforms only will let me list the AccountNum relative to the ParcelNum, but it doesn't seem to allow for a sub-subform. (does that make sense?) Basically, I will have multiple accounts per parcel number, and multiple owners per account. Actually, I can have the same owner, but with different ownership stats multiple times per account, which is why I used the OwnerID for a key. Also, the ParcelNum field in this third table is because each ownership interest within an account can refer to a different parcel number also. This creates something of a triangle where I may want all of the owners with the same account number AND the same parcel number. I assume that I want to keep the same name, since I will always compare it to ParcelNum from other tables?
That's really the biggest part of it. The other tables are important too, for sure, but these are the most pressing parts, because once I get those tables functioning properly, it will be so much easier to do my job! Any suggestions or other critiques would be great. I think I went a little crazy with keys at one point because I was having trouble ending up with subforms based on queries that were uneditable, and was told that I needed all of my relationships to be from a key to another key to be able to have updateable suforms and queries. I haven't had much luck there either way.
Thanks for reading all of this! Looking forward to constructive assistance...
Start Free Trial
