I have three tables (call them: ap,ods,je) where the records in these tables are matched up to the correct matching record (as determined by the underlying business logic). This match can be made in any of the six ways including matching to a record in its own table. To accomplish this I created a matchedWith table that stores the recordID of the two records to be matched together and some supporting information.
I need to be able to get back to the record in the original table from this match. In order for this to work I need to be able to ensure that the recordID's are unique, not only in their individual tables but in the combination of all three as well. This is easily accomplished in Oracle by creating one sequence that all three tables use. How can I accomplish this in Access? The only solution I have thought of is to union the three recordID's, take the max and add one to that value every time I need to add a new record, this is not a feasible solution.
Well my not being an expert or anything I will say you were pretty much on the right track from my perspective. Take a look at the small sample database I posted below and see if that addresses what you are trying to do. I think that is what you were saying but not sure. Created 3 individual tables, then created one table that combined all of them together. They all have their own unique ID's...(primary keys), yet, in the one JoinTable they are all combined in what I called the tblDetails and gave that one an ID of it's own, with the others included. Anyway, take a look and see if that's what you were talking about. If not them maybe someone else can give you a hand. Just thought I'd give it a try.