add new record from linked table

[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

 

[Philip Herlihy]

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]

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?

 

[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

 

[Philip Herlihy]

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]

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.

 

[mcnewsxp]

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

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]

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]

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 ...

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]

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