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

"mcnews" <(E-Mail Removed)> wrote in message
news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
> 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

"Philip Herlihy" <(E-Mail Removed)> wrote in message
news:gTI%n.177409$sD7.11419@hurricane...
>
> "mcnews" <(E-Mail Removed)> wrote in message
> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>> 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.
>

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?

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

mcnewsxp wrote:
> "Philip Herlihy" <(E-Mail Removed)> wrote in message
> news:gTI%n.177409$sD7.11419@hurricane...
>> "mcnews" <(E-Mail Removed)> wrote in message
>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>> 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.
>>
>
> 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" <(E-Mail Removed)> wrote in message
news:i1pnj1$6v3$(E-Mail Removed)...
>
> "Philip Herlihy" <(E-Mail Removed)> wrote in message
> news:gTI%n.177409$sD7.11419@hurricane...
>>
>> "mcnews" <(E-Mail Removed)> wrote in message
>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>> 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.
>>
>
> 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

"Philip Herlihy" <(E-Mail Removed)> wrote in message
news:Pg60o.244844$Hs4.31540@hurricane...
>
> "mcnewsxp" <(E-Mail Removed)> wrote in message
> news:i1pnj1$6v3$(E-Mail Removed)...
>>
>> "Philip Herlihy" <(E-Mail Removed)> wrote in message
>> news:gTI%n.177409$sD7.11419@hurricane...
>>>
>>> "mcnews" <(E-Mail Removed)> wrote in message
>>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>>> 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.
>>>
>>
>> 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.

"John Spencer" <(E-Mail Removed)> wrote in message
news:i1puql$5j2$(E-Mail Removed)...
>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
>
> mcnewsxp wrote:
>> "Philip Herlihy" <(E-Mail Removed)> wrote in message
>> news:gTI%n.177409$sD7.11419@hurricane...
>>> "mcnews" <(E-Mail Removed)> wrote in message
>>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>>> 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.
>>>
>>
>> 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]);

"mcnewsxp" <(E-Mail Removed)> wrote in message
news:i1sk6t$422$(E-Mail Removed)...
>
> "Philip Herlihy" <(E-Mail Removed)> wrote in message
> news:Pg60o.244844$Hs4.31540@hurricane...
>>
>> "mcnewsxp" <(E-Mail Removed)> wrote in message
>> news:i1pnj1$6v3$(E-Mail Removed)...
>>>
>>> "Philip Herlihy" <(E-Mail Removed)> wrote in message
>>> news:gTI%n.177409$sD7.11419@hurricane...
>>>>
>>>> "mcnews" <(E-Mail Removed)> wrote in message
>>>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>>>> 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.
>>>>
>>>
>>> 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)

"Philip Herlihy" <(E-Mail Removed)> wrote in message
news:nxp0o.32092$AS4.11574@hurricane...
>
> "mcnewsxp" <(E-Mail Removed)> wrote in message
> news:i1sk6t$422$(E-Mail Removed)...
>>
>> "Philip Herlihy" <(E-Mail Removed)> wrote in message
>> news:Pg60o.244844$Hs4.31540@hurricane...
>>>
>>> "mcnewsxp" <(E-Mail Removed)> wrote in message
>>> news:i1pnj1$6v3$(E-Mail Removed)...
>>>>
>>>> "Philip Herlihy" <(E-Mail Removed)> wrote in message
>>>> news:gTI%n.177409$sD7.11419@hurricane...
>>>>>
>>>>> "mcnews" <(E-Mail Removed)> wrote in message
>>>>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>>>>> 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.
>>>>>
>>>>
>>>> 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 ...
>

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.

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

mcnewsxp wrote:
> "Philip Herlihy" <(E-Mail Removed)> wrote in message
> news:Pg60o.244844$Hs4.31540@hurricane...
>> "mcnewsxp" <(E-Mail Removed)> wrote in message
>> news:i1pnj1$6v3$(E-Mail Removed)...
>>> "Philip Herlihy" <(E-Mail Removed)> wrote in message
>>> news:gTI%n.177409$sD7.11419@hurricane...
>>>> "mcnews" <(E-Mail Removed)> wrote in message
>>>> news:0dfe989c-4098-43f3-91db-(E-Mail Removed)...
>>>>> 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.
>>>>
>>> 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.
>
>