It is not necessary to control the numbers assigned to records. Just let
autonumber work for each table and assign the key field a name that conveys
the identity of the table - i.e., "Parent ID", "Mary ID", "Jill ID", etc.
When each table has been created, create the relationship between tables in
the relationship view, and relate each key field from the various child
tables to a numeric field in the parent table that has the same name as the
child's identity key field - i.e., "Mary ID", "Jill ID", etc.
When records are added to the relational database an autogenerated number is
added to each child key field as well as to the corresponding identity field
in the parent table. It all happens automatically by one-to-many
relationship you prescribe, and you don't have to control or even think
about assigning records a number within a specific numeric range.. Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10141
Can this idea work for you?
"Adrian John" John@discussions.microsoft.com> wrote in message
>I am trying to develop a database that has one parent table to many
> tables.The parent table uses a sequential ID number e.g starting at 1000
> child tables have their own 6 figure number based on name i.e Mary's
> begin with 299 Jill's 101 and so on. How do I get Access to combine and
> select the next available 'empty' child number entry based on the childs
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10141
> Parent 1000 Mary 299100
> Parent 1001 Mary 299101
> Parent 1002 Jill 101000
> Parent 1003 Mary 299102
> Parent 1004 Jill 101001
> Hope this makes some sense.