Jim, we aren't confused at all. Your system is. The reason you are having trouble is that until you CLEARLY define your requirements and methods, you aren't ready to code doodlum-squat. You think you have defined everything, but you haven't. Here is the point that is going to bite you on the butt.
a property can change size at every sale transaction. It can be joined with another property to form a larger property, or it can be subdivided into one or more smaller properties. All are given new Title ID's, which are date/time stamped.
Your two-number table is inadequate in that you need to be able to know WHEN that entry was made as such, and because you also need to know whether the resulting property was a simple transfer of title or a size change (split or join, which one is actually immaterial except that you have know the nature of the change.)
Think about this table as a possible starting point:
tblTitleChange:
XactNo, autonumber (long), an arbitrary number used for reports and control forms.
SrcTitle, LONG, the source title for this transaction
DstTitle, LONG, the destination title for this transaction
XactDate, DATE, the date (optionally, time if you needed it) of the transaction
XactType, SHORT TEXT (1 char) - T for transfer, J for join, S for split
(if you need to track who entered this transaction, add to this table)
(if you wish to include property dimensions resulting from the split/join, add to this table - but in this case a separate table on property ID / dimensions / owner might be a better choice for you.)
Here is the next conundrum you need to consider: Suppose for the sake of argument that property 1234 and 1345 are JOINED for a new property with title 1456. Next year, property 1456 splits into 1567 and 1678. Now someone comes in and says they had an old, unresolved and improperly recorded lien on property 1234 and need to find out what happened to it. Your report won't do that in one shot, because the record that correctly describes 1234 would stop at 1456, but that doesn't describe the current state of the property. This requires some type of topological sort algorithm that can follow properties, and it would be a hum-dinger to manage. Part A of the hum-dinger is to be able to follow links, probably via a callable VBA function to find the MOST RECENT transaction that involved source-title X which is (or derives from) the selected input title number. Part B, though, is that if a split or join occurred, your function ceased to be a one-to-one search and became a one-to-many search.
In essence, to know where the property is now, you have to ask yourself if that question can even be answered. (No, not kidding.) Suppose that 1234 and 1345 were both rectangles with the long axis parallel to the equator and that the merger of those properties left 1456 as a square property. The split then caused 1567 and 1678 to be rectangles with the long axis perpendicular to the equator. Now, tell me where is property 1234? You have a can of industrial grade worms there, Jim.
Now, just so you don't think I'm dumping on your cry for help, here is a comment on at least part of your concern: For the simple issue for reports based on any one property title, reports DON'T CARE whether you sort by old titles first or new titles first. Queries can be built to search based on EITHER of the title number columns (as two separate queries, one that focuses on old number, one that focuses on new number.) Having a many-many table lets you have that flexibility to choose which column "drives" the process. Access truly is achiral (which means it doesn't care whether it is doing a left-handed or right-handed search.)
