This is something that on the surface seems like it should be simple,
but I can't think of a way to do this. I have a table that is a list
of "jobs", which users create and use. It has a single autonumber key
field. Within a job, there can be multiple orders. Logically, there'd
be an orders table that is a child of the job table. This orders table
has a long integer field that matches it to the master job autonumber
field. Unfortunately, we don't always have control over orders, and
there can often be cases where orders are created that don't yet have a
master job record in our local database. I import data from HQ that
relates to these orders. That data has it's own unique identifiers,
and also contains some job-level info. I can import this data into our
local orders table, so we always have a local order record that relates
to HQ's orders. What I would LIKE to do is append a new job record for
every incoming order record that does not yet have a matching job, and
of course populate the matching long integer field in the orders table
to make it relate to the master record it just caused to be created.
It's easy to append new job records for the "orphaned" orders that are
imported. But since the key field of the parent table is an
autonumber, I don't know how to relate that back to the child table so
that is no longer orphaned.
Does that make sense?
but I can't think of a way to do this. I have a table that is a list
of "jobs", which users create and use. It has a single autonumber key
field. Within a job, there can be multiple orders. Logically, there'd
be an orders table that is a child of the job table. This orders table
has a long integer field that matches it to the master job autonumber
field. Unfortunately, we don't always have control over orders, and
there can often be cases where orders are created that don't yet have a
master job record in our local database. I import data from HQ that
relates to these orders. That data has it's own unique identifiers,
and also contains some job-level info. I can import this data into our
local orders table, so we always have a local order record that relates
to HQ's orders. What I would LIKE to do is append a new job record for
every incoming order record that does not yet have a matching job, and
of course populate the matching long integer field in the orders table
to make it relate to the master record it just caused to be created.
It's easy to append new job records for the "orphaned" orders that are
imported. But since the key field of the parent table is an
autonumber, I don't know how to relate that back to the child table so
that is no longer orphaned.
Does that make sense?