Problem with join on same table in two different dbs

[rdemyan via AccessMonster.com]

I have two databases, db1 and db2, with the same table, TableA. I want to
select the records from TableA in db1 that have a LAST_UPDATE SomeDate.
Then I want to get the identical records in TableA from db2. However the
LAST_UPDATE dates will be different between db1 and db2. That's the point. If
they are different, then there were changes made to the record in db1. I'm
going to then process this further to find out what the changes were.

But right now I just want to construct an SQL statement that I can use in
code to return the data that I need. Ultimately, I'm going to run this code
on over 100 tables. That's why I need to apply some type of filter (i.e.
LAST_UPDATE SomeDate) in order to have this overall process completed in a
short time.

I guess the statement could just get all records where the LAST_UPDATE is
different between TableA in db1 and TableA in db2. But speed is important,
because ultimately I will apply it to the 100 tables.

Note that db1 is the current database and db2 is an external database.

So, for example, I would have thought the following would work:

------------------------------------------------------------------------------
--

SELECT A.*, B.* FROM [TableA] As A
LEFT JOIN (SELECT * FROM [C:\.....].[TableA]) AS B ON (A.FIELD1 = B.FIELD1)
AND (A.FIELD2= B.FIELD2) AND (A.FIELD3= B.FIELD3)
WHERE A.LAST_UPDATE #SomeDate#

------------------------------------------------------------------------------
--
What's happening right now is that all records from the table are being
returned. In the test case I should have had 1 record returned and instead I
got all of them (1500+)

Thanks.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[Lyle Fairfield]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in
news:Xn******** *************** ***********@127 .0.0.1:

2. AccessMonster sells ads and makes money off of republishing
content that was posted on Usenet, a public forum where no one
profits from the posts.

"off of"?

Ewwwwwwww!

--
Lyle Fairfield

[Larry Linson]

"David W. Fenton" wrote

And Larry did a very poor job of wording it, as
it was not until a few posts back that I realized
he was criticizing *only* AccessMonster and
not the present poster.

Actually, David, if you'll re-read a few more times, you'll see that I was
not criticizing Access Monster, but only trying to explain why I think Lyle
and others are so un-fond of it.

As you said elsewhere in this thread about your posts, I post material in
hopes that it will be useful to someone. If Access Monster, Google Groups,
and others re-post the material and do not charge people to access it, they
are expediting what I try to do.

And, there's no practical way of preventing people from using our posts in
CD collections of material they gather online and sell. I've had very polite
replies agreeing to remove my posts from such collections, but as I was
never willing to send $$$ for a copy of the CD, I'll never know if they did
or not. And, once I realized it was fruitless, I stopped spending time and
effort complaining about the practice.

Larry Linson

['69 Camaro]

Hi, David.

Yes, but they actually provide a very useful service, quite unlike
AccessMonster.

I just checked AccessMonster's search engine. It goes back 3.5 years, so
they've made an effort to improve their Web site, but searches are still very
limited in scope and flexibility. Google Groups' search capability is much,
much better. However, Google Groups has been cleaning out its archives. A lot
of messages and threads are gone. About 40% of my posts aren't listed in
advanced searches, so I suppose anyone who's been posting for several years or
more probably has a similar amount missing.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** **********@127. 0.0.1...

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:vK******** *************** *******@adelphi a.com:

>>I've switched over and am now poting in the Usenet group.

From Google Groups? Don't they show ads and "sponsored links"
with posts, too?
<g, d, & r>

Yes, but they actually provide a very useful service, quite unlike
AccessMonster.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

['69 Camaro]

Hi, David.

>>Frankly, I'm glad he has plonked me.

You may not like his demeanor at times, but Lyle is very, very
generous with his considerable knowledge and expertise. This is
not an event to celebrate.

I find Lyle's advice to be highly idiosyncratic and often not
helpful. I plonked him a long time ago because of that.

I've found I've learned much from the denizens of the Access newsgroups. We
have some very talented and generous experts who post here. You, Larry, and
Lyle are three of them. I wouldn't want rdemyan or anyone else who wants to
learn more about Access and databases to miss out on a treasure trove of
knowledge over a disagreement in a discussion.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:UL******** *************** *******@adelphi a.com:

>>I've mentioned several times that I'm a
consultant in this forum and by profit I naturally thought he
meant.....

And that's why Larry tried so hard to explain it's
AccessMonster. com that Lyle takes umbrage with, not that you're
profiting from the code he provides freely.

And Larry did a very poor job of wording it, as it was not until a
few posts back that I realized he was criticizing *only*
AccessMonster and not the present poster.

[]

>>Frankly, I'm glad he has plonked me.

You may not like his demeanor at times, but Lyle is very, very
generous with his considerable knowledge and expertise. This is
not an event to celebrate.

I find Lyle's advice to be highly idiosyncratic and often not
helpful. I plonked him a long time ago because of that.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:Zt******** *************** *******@adelphi a.com:

However, Google Groups has been cleaning out its archives. A lot
of messages and threads are gone. About 40% of my posts aren't
listed in advanced searches, so I suppose anyone who's been
posting for several years or more probably has a similar amount
missing.

I can find none of my posts missing going back to 1994. Are you sure
you're looking under the right email addresses?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

['69 Camaro]

Hi, David.

Are you sure
you're looking under the right email addresses?

Yes. I've posted under three E-mail addresses, but whether I add those up or
check the count by author as "69 Camaro," the number is a few thousand shy of
the sum of the number of posts in my Outlook Express Sent box, the number posted
through Google Groups, and the number posted through Microsoft Online
Communities. I have proof of nearly 5,500 posts, all as '69 Camaro. There's a
few hundred more, but I'd have to go through old backups for proof.

This lists 3,440 posts (but at least 7 aren't mine):

http://groups.google.com/groups?as_q...=2006&safe=off

The profile (E-mail address) for the most recent post shows only 3,565 posts.
The other two address profiles show 100 posts and 27 posts. That's almost 2K
shy of what it should be.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:Zt******** *************** *******@adelphi a.com:

>However, Google Groups has been cleaning out its archives. A lot
of messages and threads are gone. About 40% of my posts aren't
listed in advanced searches, so I suppose anyone who's been
posting for several years or more probably has a similar amount
missing.

I can find none of my posts missing going back to 1994. Are you sure
you're looking under the right email addresses?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

['69 Camaro]

Hi, David.

And it's not just my own messages that are missing from searches. Messages and
occasionally whole threads are missing from the archive. I've looked for
message ID's I downloaded from my ISP's news server, but couldn't find them in
Google Groups. I've also looked for discussion threads that I'd read a few
years ago and wanted to refresh my memory, but they aren't in Google Groups any
more.

For example, I wanted to review the two discussions on Jet's covered queries
from a few years ago. As I recall, there were two threads of discussions in the
Access newsgroups plus a few threads where covered queries were mentioned, but
not really discussed. I could only find one thread where it was mentioned, but
it wasn't discussed at length. I know that covered queries aren't a hot topic
to discuss in the Access newsgroups, but doesn't it seem like in all these years
it would have been mentioned a good number of times and discussed at length at
least once? Or is this a Twilight Zone episode where I'm the only one who
remembers what "used to be there, I'd swear it!"?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAMwrote in
message news:LN******** *************** *******@adelphi a.com...

Hi, David.

>Are you sure
you're looking under the right email addresses?

Yes. I've posted under three E-mail addresses, but whether I add those up or
check the count by author as "69 Camaro," the number is a few thousand shy of
the sum of the number of posts in my Outlook Express Sent box, the number
posted through Google Groups, and the number posted through Microsoft Online
Communities. I have proof of nearly 5,500 posts, all as '69 Camaro. There's
a few hundred more, but I'd have to go through old backups for proof.

This lists 3,440 posts (but at least 7 aren't mine):

http://groups.google.com/groups?as_q...=2006&safe=off

The profile (E-mail address) for the most recent post shows only 3,565 posts.
The other two address profiles show 100 posts and 27 posts. That's almost 2K
shy of what it should be.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

>"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:Zt******** *************** *******@adelphi a.com:

>>However, Google Groups has been cleaning out its archives. A lot
of messages and threads are gone. About 40% of my posts aren't
listed in advanced searches, so I suppose anyone who's been
posting for several years or more probably has a similar amount
missing.

I can find none of my posts missing going back to 1994. Are you sure
you're looking under the right email addresses?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"'69 Camaro" wrote

I've found I've learned much from the denizens
of the Access newsgroups. We have some very
talented and generous experts who post here.
You, Larry, and Lyle are three of them.

Thanks for the very kind words, Gunny.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:Bs******** *************** *******@adelphi a.com:

And it's not just my own messages that are missing from searches.
Messages and occasionally whole threads are missing from the
archive. I've looked for message ID's I downloaded from my ISP's
news server, but couldn't find them in Google Groups. I've also
looked for discussion threads that I'd read a few years ago and
wanted to refresh my memory, but they aren't in Google Groups any
more.

There was a time when Google (or Dejanews) restored the archives
from copies they got from all over, and they weren't honoring
No-Archive headers -- they later removed all of those. Have you ever
used that?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

['69 Camaro]

Hi, David.

they weren't honoring
No-Archive headers -- they later removed all of those. Have you ever
used that?

No. But here's something I noticed way back when Google Groups first became
"groups-beta.google.com " near the end of 2004. I'd search a topic, such as an
error message, and not get nearly as many hits as I expected. Then I'd do the
same search at groups.google.c a or groups.google.c o.uk, and those searches
revealed 5% to 35% more results, as if they were searching a different database
of posts. Eventually, all the other English-speaking Google domains switched to
the same search methodology as the first groups-beta did and inherited the new
Google Groups interface and what looked like (to me) fewer posts to search from
than they'd had before the changeover.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:Bs******** *************** *******@adelphi a.com:

>And it's not just my own messages that are missing from searches.
Messages and occasionally whole threads are missing from the
archive. I've looked for message ID's I downloaded from my ISP's
news server, but couldn't find them in Google Groups. I've also
looked for discussion threads that I'd read a few years ago and
wanted to refresh my memory, but they aren't in Google Groups any
more.

There was a time when Google (or Dejanews) restored the archives
from copies they got from all over, and they weren't honoring
No-Archive headers -- they later removed all of those. Have you ever
used that?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/