1

So the root of this problem may lie in poor database design, some of the way this is set up is inherited from older versions. I just couldn't figure out a better way to do this.

I have four tables linked by the same field: [OBJECTID]. Each table is linked to an Access Form that controls the data. It is important that these tables be separate as the data is georeferenced and needs to be mapped separately, however they inherit several fields from one another by default.

Most of the time, the tables are in a one-to-one-to-one-to-one relationship, however occasionally, there is only data for the first table, and occasionally, there is only data for the second, third and fourth form.

Right now, the [OBJECTID] field in the first table is set to datatype autonumber, so that all subsequent linked records in the other tables can inherit that number. For the cases where the record in Tbl1 are not entered via Form1, it is easy enough to just assign a number that does not conflict with any current number, but how do I avoid assigning a number that could conflict with some future [OBJECTID] generated by the autonumber field in Tbl1?

Sorry if that is confusing! Thanks in advance for helping me think this through....

Erik A
  • 31,639
  • 12
  • 42
  • 67
mizmay
  • 11
  • 1

1 Answers1

0

If the design is correct, there should be a relationship with referential integrity between tbl1 and table 2/3/4. Since you mention that occasionally, there is only data for the second, third and fourth form that means we have no referential integrity here :-/.

I would identify the fields that are common to all 4 tables, and create a "main" table with those, meaning that the main table MUST be filled. Then you create a 1 to 0,1 relationship to the other 4 tables, with an outer join, their PK beeing then a Long Integer.

For the source of your forms 1 to 4, use an outer join between MainTable and T1/2/3/4. The "subtables" will then inherit the PK of the main table.

Hope I am not too obscure.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Yes that absolutely makes sense. I'll make a main table with only one field that autonumbers OBJECTID for all the other fields. Thank you! – mizmay Mar 19 '12 at 20:58
  • Nope, actually I ended up creating a table that stores the last number assigned and keeping track of OBJECTID there instead of autonumber at all. Then creating a global function to assign it, like this: http://www.techonthenet.com/access/modules/sequential_nbr.php – mizmay Mar 19 '12 at 21:14
  • The MainTbale concept would be much easier if you ever need to JOIN the 4 sub tables, specially because Jet does not make it easy to have full outer joins. – iDevlop Mar 20 '12 at 06:53