add new record from linked table

Post by mcnews
MDB 1 contains two tables and MDB 2 contains one table. both tables
in MDB 1 need to link to link the table in MDB 2. the table in MDB 1
will be updated with new records periodically. when the new records
are imported into MDB 2 what is the best way to have them appear in
the two tables in MDB 1?
tia,
mcnewsxp

Wherever possible, data should be in just one place, and is often divided
between tables to achieve this - a record in one contains a reference to a
record in another. Tables can reside in different database file but be
linked (rather than imported) so that a query which draws information from
more than one file will benefit from the most up-to-date data. If you
import, rather than link, you'll forever be clumsily refreshing. Getting
your table design right is more than half the battle: the way you put your
question makes me think there may be some issues there.

Phil, London

mcnewsxp

2010-07-16 13:41:55 UTC

MDB 2 will house all of the records thst come from outside sources. this is
part of a study. MDB 2 will contain records for al of the specimens that
are sent to a central location and will imported to MDB 2. MDB 1 will have
two tables that will store test results for two different tests. MDB 1 has
a link the the table in MDB 2. the tests are performed on the specimens that
are stored in MDB 2. the table in MDB 2 and the two tables in MDB 1 share a
common ID column.

so again, when new records are imported into the table contained in MDB 2
how do i keep the two tables in MDB 1 up to date with the new common ID
column - automatically?

John Spencer

2010-07-16 15:45:24 UTC

I would guess that you would need to find a way to run an append query to add
the new records. There is no setting or method to do this automatically.

Assumptions: MDB 1 has a link to the table(s) in MDB 2

Without more details, the generic append queries might look like

INSERT INTO MDB1TableA (SpecimenID)
SELECT SpecimenID
FROM MDB2Table LEFT JOIN MDB1TableA
ON MDB2Table.SpecimenID = MDB1TableA.SpecimenID
WHERE MDB1TableA.SpecimenID is NULL

and

INSERT INTO MDB1TableB (SpecimenID)
SELECT SpecimenID
FROM MDB2Table LEFT JOIN MDB1TableB
ON MDB2Table.SpecimenID = MDB1TableB.SpecimenID
WHERE MDB1TableB.SpecimenID is NULL

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

MDB 2 will house all of the records thst come from outside sources. this is
part of a study. MDB 2 will contain records for al of the specimens that
are sent to a central location and will imported to MDB 2. MDB 1 will have
two tables that will store test results for two different tests. MDB 1 has
a link the the table in MDB 2. the tests are performed on the specimens that
are stored in MDB 2. the table in MDB 2 and the two tables in MDB 1 share a
common ID column.
so again, when new records are imported into the table contained in MDB 2
how do i keep the two tables in MDB 1 up to date with the new common ID
column - automatically?

mcnewsxp

2010-07-17 16:08:43 UTC

Post by John Spencer
I would guess that you would need to find a way to run an append query to
add the new records. There is no setting or method to do this
automatically.
Assumptions: MDB 1 has a link to the table(s) in MDB 2
Without more details, the generic append queries might look like
INSERT INTO MDB1TableA (SpecimenID)
SELECT SpecimenID
FROM MDB2Table LEFT JOIN MDB1TableA
ON MDB2Table.SpecimenID = MDB1TableA.SpecimenID
WHERE MDB1TableA.SpecimenID is NULL
and
INSERT INTO MDB1TableB (SpecimenID)
SELECT SpecimenID
FROM MDB2Table LEFT JOIN MDB1TableB
ON MDB2Table.SpecimenID = MDB1TableB.SpecimenID
WHERE MDB1TableB.SpecimenID is NULL
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Wherever possible, data should be in just one place, and is often
divided between tables to achieve this - a record in one contains a
reference to a record in another. Tables can reside in different
database file but be linked (rather than imported) so that a query which
draws information from more than one file will benefit from the most
up-to-date data. If you import, rather than link, you'll forever be
clumsily refreshing. Getting your table design right is more than half
the battle: the way you put your question makes me think there may be
some issues there.

MDB 2 will house all of the records thst come from outside sources. this
is part of a study. MDB 2 will contain records for al of the specimens
that are sent to a central location and will imported to MDB 2. MDB 1
will have two tables that will store test results for two different
tests. MDB 1 has a link the the table in MDB 2. the tests are performed
on the specimens that are stored in MDB 2. the table in MDB 2 and the
two tables in MDB 1 share a common ID column.
so again, when new records are imported into the table contained in MDB 2
how do i keep the two tables in MDB 1 up to date with the new common ID
column - automatically?

yes, i figured that part out. i was hoping there'd be a way to trigger the
inserts. here's my sql:
INSERT INTO [M2 Drug Resistance] ( [cdc id #] )
SELECT cdcid
FROM [StudyA]
WHERE [StudyA].cdcid NOT IN (SELECT [TestA].[cdc
id #] FROM [TestA]);

INSERT INTO [TestB] ( [cdc id #] )
SELECT cdcid
FROM [StudyA]
WHERE [StudyA].cdcid NOT IN (SELECT [TestB].[cdc id #] FROM [TestB]);

Philip Herlihy

2010-07-16 23:52:16 UTC

Wherever possible, data should be in just one place, and is often divided
between tables to achieve this - a record in one contains a reference to
a record in another. Tables can reside in different database file but be
linked (rather than imported) so that a query which draws information
from more than one file will benefit from the most up-to-date data. If
you import, rather than link, you'll forever be clumsily refreshing.
Getting your table design right is more than half the battle: the way you
put your question makes me think there may be some issues there.

MDB 2 will house all of the records thst come from outside sources. this
is part of a study. MDB 2 will contain records for al of the specimens
that are sent to a central location and will imported to MDB 2. MDB 1
will have two tables that will store test results for two different tests.
MDB 1 has a link the the table in MDB 2. the tests are performed on the
specimens that are stored in MDB 2. the table in MDB 2 and the two tables
in MDB 1 share a common ID column.
so again, when new records are imported into the table contained in MDB 2
how do i keep the two tables in MDB 1 up to date with the new common ID
column - automatically?

Again: Getting your table design right is more than half the battle: the way
you put your question makes me think there may be some issues there.

What you seem to be asking (and John has answered) is how to create new
test-result records which are empty apart from the ID of the specimen to
which they will refer. Why would you want to do that? Unless MDB2 isn't an
accumulating table of specimens but only contains the latest batch, perhaps?
If that's the case (and of course I'm guessing) then in my view it would
make more sense to append the new batch to a "permanent" table of Specimens.
Then add a new test-result record to the appropriate table when you have
some data to put in it.

It's certainly possible that I'm missing something (and I'm certainly less
qualified than John to pontificate) but the experience I do have suggests
that if you are drawn to add some "procedural" code (think Visual Basic) to
make your data model work then it's well worth re-examining your table
structure. When you add a test result, you need to be able to refer to a
specific Specimen. That doesn't mean you have to pre-populate the Results
table(s) when the specimens arrive (before they are tested). I see this as
a situation in which you'd use a form. You'd select a Specimen from the
table of available specimens, type in values for test results, and only at
that point create a new record in the Results table. You could list untested
specimens using a simple "outer join" query (which might be used to filter
specimens for selection in your form).

Phil

mcnewsxp

2010-07-17 16:02:37 UTC

Wherever possible, data should be in just one place, and is often
divided between tables to achieve this - a record in one contains a
reference to a record in another. Tables can reside in different
database file but be linked (rather than imported) so that a query which
draws information from more than one file will benefit from the most
up-to-date data. If you import, rather than link, you'll forever be
clumsily refreshing. Getting your table design right is more than half
the battle: the way you put your question makes me think there may be
some issues there.

MDB 2 will house all of the records thst come from outside sources. this
is part of a study. MDB 2 will contain records for al of the specimens
that are sent to a central location and will imported to MDB 2. MDB 1
will have two tables that will store test results for two different
tests. MDB 1 has a link the the table in MDB 2. the tests are performed
on the specimens that are stored in MDB 2. the table in MDB 2 and the
two tables in MDB 1 share a common ID column.
so again, when new records are imported into the table contained in MDB 2
how do i keep the two tables in MDB 1 up to date with the new common ID
column - automatically?

Again: Getting your table design right is more than half the battle: the
way you put your question makes me think there may be some issues there.
What you seem to be asking (and John has answered) is how to create new
test-result records which are empty apart from the ID of the specimen to
which they will refer. Why would you want to do that? Unless MDB2 isn't
an accumulating table of specimens but only contains the latest batch,
perhaps? If that's the case (and of course I'm guessing) then in my view
it would make more sense to append the new batch to a "permanent" table of
Specimens. Then add a new test-result record to the appropriate table when
you have some data to put in it.
It's certainly possible that I'm missing something (and I'm certainly less
qualified than John to pontificate) but the experience I do have suggests
that if you are drawn to add some "procedural" code (think Visual Basic)
to make your data model work then it's well worth re-examining your table
structure. When you add a test result, you need to be able to refer to a
specific Specimen. That doesn't mean you have to pre-populate the Results
table(s) when the specimens arrive (before they are tested). I see this
as a situation in which you'd use a form. You'd select a Specimen from
the table of available specimens, type in values for test results, and
only at that point create a new record in the Results table. You could
list untested specimens using a simple "outer join" query (which might be
used to filter specimens for selection in your form).

actually i want what i want, but apparently can't have it with Access.

Philip Herlihy

2010-07-17 21:47:01 UTC

Wherever possible, data should be in just one place, and is often
divided between tables to achieve this - a record in one contains a
reference to a record in another. Tables can reside in different
database file but be linked (rather than imported) so that a query
which draws information from more than one file will benefit from the
most up-to-date data. If you import, rather than link, you'll forever
be clumsily refreshing. Getting your table design right is more than
half the battle: the way you put your question makes me think there may
be some issues there.

MDB 2 will house all of the records thst come from outside sources.
this is part of a study. MDB 2 will contain records for al of the
specimens that are sent to a central location and will imported to MDB
2. MDB 1 will have two tables that will store test results for two
different tests. MDB 1 has a link the the table in MDB 2. the tests are
performed on the specimens that are stored in MDB 2. the table in MDB 2
and the two tables in MDB 1 share a common ID column.
so again, when new records are imported into the table contained in MDB
2 how do i keep the two tables in MDB 1 up to date with the new common
ID column - automatically?

Again: Getting your table design right is more than half the battle: the
way you put your question makes me think there may be some issues there.
What you seem to be asking (and John has answered) is how to create new
test-result records which are empty apart from the ID of the specimen to
which they will refer. Why would you want to do that? Unless MDB2 isn't
an accumulating table of specimens but only contains the latest batch,
perhaps? If that's the case (and of course I'm guessing) then in my view
it would make more sense to append the new batch to a "permanent" table
of Specimens. Then add a new test-result record to the appropriate table
when you have some data to put in it.
It's certainly possible that I'm missing something (and I'm certainly
less qualified than John to pontificate) but the experience I do have
suggests that if you are drawn to add some "procedural" code (think
Visual Basic) to make your data model work then it's well worth
re-examining your table structure. When you add a test result, you need
to be able to refer to a specific Specimen. That doesn't mean you have
to pre-populate the Results table(s) when the specimens arrive (before
they are tested). I see this as a situation in which you'd use a form.
You'd select a Specimen from the table of available specimens, type in
values for test results, and only at that point create a new record in
the Results table. You could list untested specimens using a simple
"outer join" query (which might be used to filter specimens for selection
in your form).

actually i want what i want, but apparently can't have it with Access.

Yes you can, but only by "automating" Access - which is a very different
idiom from the relational one and adds orders of magnitude more complexity.
The best reference from a quick web search is this one:
http://en.wikipedia.org/wiki/OLE_Automation

The examples use Perl to automate Excel, but the principles are the same.
I've written plenty of code like this - but only when there's a very good
reason (and you've chosen not to disclose why a simpler and cleaner approach
won't suit). Happy coding ...

Phil (over and out)

mcnewsxp

2010-07-18 15:03:19 UTC

Wherever possible, data should be in just one place, and is often
divided between tables to achieve this - a record in one contains a
reference to a record in another. Tables can reside in different
database file but be linked (rather than imported) so that a query
which draws information from more than one file will benefit from the
most up-to-date data. If you import, rather than link, you'll forever
be clumsily refreshing. Getting your table design right is more than
half the battle: the way you put your question makes me think there
may be some issues there.

MDB 2 will house all of the records thst come from outside sources.
this is part of a study. MDB 2 will contain records for al of the
specimens that are sent to a central location and will imported to MDB
2. MDB 1 will have two tables that will store test results for two
different tests. MDB 1 has a link the the table in MDB 2. the tests are
performed on the specimens that are stored in MDB 2. the table in MDB
2 and the two tables in MDB 1 share a common ID column.
so again, when new records are imported into the table contained in MDB
2 how do i keep the two tables in MDB 1 up to date with the new common
ID column - automatically?

Again: Getting your table design right is more than half the battle: the
way you put your question makes me think there may be some issues there.
What you seem to be asking (and John has answered) is how to create new
test-result records which are empty apart from the ID of the specimen to
which they will refer. Why would you want to do that? Unless MDB2
isn't an accumulating table of specimens but only contains the latest
batch, perhaps? If that's the case (and of course I'm guessing) then in
my view it would make more sense to append the new batch to a
"permanent" table of Specimens. Then add a new test-result record to the
appropriate table when you have some data to put in it.
It's certainly possible that I'm missing something (and I'm certainly
less qualified than John to pontificate) but the experience I do have
suggests that if you are drawn to add some "procedural" code (think
Visual Basic) to make your data model work then it's well worth
re-examining your table structure. When you add a test result, you need
to be able to refer to a specific Specimen. That doesn't mean you have
to pre-populate the Results table(s) when the specimens arrive (before
they are tested). I see this as a situation in which you'd use a form.
You'd select a Specimen from the table of available specimens, type in
values for test results, and only at that point create a new record in
the Results table. You could list untested specimens using a simple
"outer join" query (which might be used to filter specimens for
selection in your form).

actually i want what i want, but apparently can't have it with Access.

Yes you can, but only by "automating" Access - which is a very different
idiom from the relational one and adds orders of magnitude more
http://en.wikipedia.org/wiki/OLE_Automation
The examples use Perl to automate Excel, but the principles are the same.
I've written plenty of code like this - but only when there's a very good
reason (and you've chosen not to disclose why a simpler and cleaner
approach won't suit). Happy coding ...

i've done that kind of automation many times with VBA and other languages.
this is not really my project. the owner uses Access a lot, but doesn't
program and instead uses macros. i was hoping to give her some ideas to
make things easier for her.

John Spencer

2010-07-18 19:30:50 UTC

Well, what you want might be possible with Access 2010 since it has
table-level macros.

With earlier versions, you could just write a procedure that would run the
update queries whenever you open the database to work with it.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County