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

[Tom van Stiphout]

On Tue, 26 Dec 2006 07:27:21 GMT, "rdemyan via AccessMonster.c om"
<u6836@uwewrote :

That's what Left Join is designed to do. Why not use an Inner Join?
-Tom.

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

[rdemyan via AccessMonster.com]

Inner Join also gives undesired result, for the SQL statement as written.

Let me put in words what I want. db1 is the current database (with all the
linked tables). db2 is a copy of the backend that was made when my app opens
for a session. When the app is closed by the user, I want to find the
differences between db1 and db2.

These differences consist of edited data, added data and deleted data. FOR
NOW, I'm just working on finding changes in data that existed when the app
was opened. Therefore I'm looking for records that have the same primary key
in each identical tableA (one in db1 and one in db2). SomeDate is the
Date/Time that is set when my app opens. The purpose of this is to filter
the data that is searched (I have 110 tables and the backend is currently 120
MB in sizer). That way I'm only looking for data that was changed during the
session (rather than checking each row in each table). Even with the code as
it currently is, where it is returning all rows and looking for differences,
the code executes over the 100 tables in less than 30 seconds. If I can get
the SQL to work correctly, it'll probably drop significantly.

Thanks.

Tom van Stiphout wrote:

>That's what Left Join is designed to do. Why not use an Inner Join?
-Tom.

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

[quoted text clipped - 35 lines]

>>
Thanks.

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

[rdemyan via AccessMonster.com]

Oops! I did in fact run code in my app that cause the 1500+ rows to change
the Last_Update (as it should).

So, here's the SQL statement I have with the INNER JOIN:

SELECT A.*, B.* FROM [TableA] As A
INNER 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#

Is this correct for what I want based on my previous post. I want the
records from linked TableA in db1 where LAST_UPDATE #SomeDate# and with
that I want to grab the corresponding records from B in db2.

Thanks.

rdemyan wrote:

>Inner Join also gives undesired result, for the SQL statement as written.

Let me put in words what I want. db1 is the current database (with all the
linked tables). db2 is a copy of the backend that was made when my app opens
for a session. When the app is closed by the user, I want to find the
differences between db1 and db2.

These differences consist of edited data, added data and deleted data. FOR
NOW, I'm just working on finding changes in data that existed when the app
was opened. Therefore I'm looking for records that have the same primary key
in each identical tableA (one in db1 and one in db2). SomeDate is the
Date/Time that is set when my app opens. The purpose of this is to filter
the data that is searched (I have 110 tables and the backend is currently 120
MB in sizer). That way I'm only looking for data that was changed during the
session (rather than checking each row in each table). Even with the code as
it currently is, where it is returning all rows and looking for differences,
the code executes over the 100 tables in less than 30 seconds. If I can get
the SQL to work correctly, it'll probably drop significantly.

Thanks.

>>That's what Left Join is designed to do. Why not use an Inner Join?
-Tom.

[quoted text clipped - 4 lines]

>>>
Thanks.

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

[Lyle Fairfield]

rdemyan via AccessMonster.c om wrote:

Inner Join also gives undesired result, for the SQL statement as written.

Let me put in words what I want. db1 is the current database (with all the
linked tables). db2 is a copy of the backend that was made when my app opens
for a session. When the app is closed by the user, I want to find the
differences between db1 and db2.

These differences consist of edited data, added data and deleted data. FOR
NOW, I'm just working on finding changes in data that existed when the app
was opened. Therefore I'm looking for records that have the same primary key
in each identical tableA (one in db1 and one in db2). SomeDate is the
Date/Time that is set when my app opens. The purpose of this is to filter
the data that is searched (I have 110 tables and the backend is currently 120
MB in sizer). That way I'm only looking for data that was changed during the
session (rather than checking each row in each table). Even with the code as
it currently is, where it is returning all rows and looking for differences,
the code executes over the 100 tables in less than 30 seconds. If I can get
the SQL to work correctly, it'll probably drop significantly.

Thanks.

Tom van Stiphout wrote:

That's what Left Join is designed to do. Why not use an Inner Join?

This is a free newsgroup and anyone may post whatever he/she wishes.

Not surprisingly, anyone may respond in any way he/she wishes.

It seems to me that you are creating/have created a large and
complicated application. It also seems to me that such an application
is beyond your present capabilities. Judging from your posts, questions
and responses, I believe that your skill-level is low to mediocre, and
your knowledge basic. Worst of all you seem to have little
understanding of logic, and almost none of the way computers and their
programs work. For whatever reason, your self-confidence is not
mitigated by these characteristics .

You choose to post from a site whose owners profit by borrowing
without any recompense or direct recognition whatever the knowledge I
and others contribute freely.
Previously, when others have implied what I am saying directly, you
have sailed on regardless.

You may have potential to be a great programmer/developer. I do not
know. But I do think you are not there yet.

I think your application will be a compendium of borrowed technical
solutions (one or two of which may be mine, I'm not sure). It may work
well and it may not. If there is a problem with it, I doubt that its
creator can analyse and correct the problem.

I believe Access development should not be "Begin, and ask on Access
Monster how to accomplish each step".

Plonk!

[rdemyan via AccessMonster.com]

So you see no educational value to anyone else in ascertaining if this is the
correct SQL to achieve the desired results?

Lyle Fairfield wrote:

>Inner Join also gives undesired result, for the SQL statement as written.

[quoted text clipped - 18 lines]

>>

>That's what Left Join is designed to do. Why not use an Inner Join?

This is a free newsgroup and anyone may post whatever he/she wishes.

Not surprisingly, anyone may respond in any way he/she wishes.

It seems to me that you are creating/have created a large and
complicated application. It also seems to me that such an application
is beyond your present capabilities. Judging from your posts, questions
and responses, I believe that your skill-level is low to mediocre, and
your knowledge basic. Worst of all you seem to have little
understandin g of logic, and almost none of the way computers and their
programs work. For whatever reason, your self-confidence is not
mitigated by these characteristics .

You choose to post from a site whose owners profit by borrowing
without any recompense or direct recognition whatever the knowledge I
and others contribute freely.
Previously, when others have implied what I am saying directly, you
have sailed on regardless.

You may have potential to be a great programmer/developer. I do not
know. But I do think you are not there yet.

I think your application will be a compendium of borrowed technical
solutions (one or two of which may be mine, I'm not sure). It may work
well and it may not. If there is a problem with it, I doubt that its
creator can analyse and correct the problem.

I believe Access development should not be "Begin, and ask on Access
Monster how to accomplish each step".

Plonk!

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

[rdemyan via AccessMonster.com]

>I think your application will be a compendium of borrowed technical

>solutions (one or two of which may be mine, I'm not sure). It may work
well and it may not. If there is a problem with it, I doubt that its
creator can analyse and correct the problem.

No need to be concerned. I will not be using the code you posted for
repositioning an external app. In the future you might consider adding a
note that you wrote the code and that others may use it, but need to include
a header that you add to your code.

May I suggest that you not respond to my posts in the future. You constant
abuse is beginning to wear thin.

--
Message posted via http://www.accessmonster.com

[Larry Linson]

"rdemyan via AccessMonster.c om" wrote

I think your application will be a compendium
of borrowed technical solutions (one or two of
which may be mine, I'm not sure). It may work
well and it may not. If there is a problem with it,
I doubt that its creator can analyse and correct
the problem.

No need to be concerned. I will not be using the
code you posted for repositioning an external app.
In the future you might consider adding a note that
you wrote the code and that others may use it, but
need to include a header that you add to your code.

FYI, US and Canadian (if I remember correctly) copyright law, (Canadian
applies to Lyle's posts), provides that an original work is automatically
copyrighted by the author when it is reduced to tangible form -- thus, at
the very least, you should credit the author if you use it whether or not
there is a note as you suggest; strictly speaking, you should obtain
permission from the author.

Many, however, believe that such permission is implied when the author posts
on the Internet, in a newsgroup or website. Some believe, quite wrongly,
that posting puts the posted item in the "public domain."

May I suggest that you not respond to my posts
in the future. You constant abuse is beginning to
wear thin.

One of Lyle's points is that you post in the newsgroup via "AccessMons ter"
which he perceives as profiting from work done "pro bono" by many in this
and in other Access newsgroups.

Another is that there is a lot that can be learned by self-study, and
materials for self-study are widely available (many of them free), and that
having done some basic self-study enables one to better understand the
answers to specific questions.

I'd point out that newsgroups are not intended for the purpose of having
"ready access to free consulting" but for learning... learning by the
poster, sometimes by the responder, and always for others who read the
exchange, as well. If you become widely perceived, as you seem to be by
Lyle, as someone who uses the newsgroup to get someone else to write your
application for you, one little piece after another, then it will be
difficult to obtain help when you need it even if it is within the intent of
newsgroups.

This is an unmoderated newsgroup; anyone can post and anyone can respond. If
you don't like, or don't appreciate, a response, you are free to ignore it
(or to reply to it, unless you become abusive which is almost certainly a
violation of the Terms of Service of your news server). Lyle is free to
ignore your posts, and he has expedited doing so with his "PLONK" (that
means he has killfiled, or twit-filtered, you in his newsreader, so that he
will no longer see your posts except when some other participant quotes you
in their posts, as I did here).

Larry Linson

[rdemyan via AccessMonster.com]

I do not see how asking for help on a single SQL statement constitues getting
others to write the code for me. This is one or two lines out of thousands.
Further, if you check some of Lyle's past responses to me you will see that
they are obnoxiously condescending and abusive. I believe I can honestly say
that I have always responded to his abusive posts politely and without
getting personal.

However, it appears that Lyle no longer wants me to post in this forum and
that MVPs such as yourself will be backing him up. Therefore, I will no
longer post in this forum.

To those that have helped me in the past, thank you!

Larry Linson wrote:

I think your application will be a compendium
of borrowed technical solutions (one or two of
which may be mine, I'm not sure). It may work
well and it may not. If there is a problem with it,
I doubt that its creator can analyse and correct
the problem.

No need to be concerned. I will not be using the
code you posted for repositioning an external app.
In the future you might consider adding a note that
you wrote the code and that others may use it, but
need to include a header that you add to your code.

FYI, US and Canadian (if I remember correctly) copyright law, (Canadian
applies to Lyle's posts), provides that an original work is automatically
copyrighted by the author when it is reduced to tangible form -- thus, at
the very least, you should credit the author if you use it whether or not
there is a note as you suggest; strictly speaking, you should obtain
permission from the author.

Many, however, believe that such permission is implied when the author posts
on the Internet, in a newsgroup or website. Some believe, quite wrongly,
that posting puts the posted item in the "public domain."

May I suggest that you not respond to my posts
in the future. You constant abuse is beginning to
wear thin.

One of Lyle's points is that you post in the newsgroup via "AccessMons ter"
which he perceives as profiting from work done "pro bono" by many in this
and in other Access newsgroups.

Another is that there is a lot that can be learned by self-study, and
materials for self-study are widely available (many of them free), and that
having done some basic self-study enables one to better understand the
answers to specific questions.

I'd point out that newsgroups are not intended for the purpose of having
"ready access to free consulting" but for learning... learning by the
poster, sometimes by the responder, and always for others who read the
exchange, as well. If you become widely perceived, as you seem to be by
Lyle, as someone who uses the newsgroup to get someone else to write your
application for you, one little piece after another, then it will be
difficult to obtain help when you need it even if it is within the intent of
newsgroups.

This is an unmoderated newsgroup; anyone can post and anyone can respond. If
you don't like, or don't appreciate, a response, you are free to ignore it
(or to reply to it, unless you become abusive which is almost certainly a
violation of the Terms of Service of your news server). Lyle is free to
ignore your posts, and he has expedited doing so with his "PLONK" (that
means he has killfiled, or twit-filtered, you in his newsreader, so that he
will no longer see your posts except when some other participant quotes you
in their posts, as I did here).

Larry Linson

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

[Larry Linson]

"rdemyan via AccessMonster.c om" <u6836@uwewro te in message
news:6b58b553c2 9de@uwe...

I do not see how asking for help on a single SQL statement constitues

getting

others to write the code for me. This is one or two lines out of

thousands.

In response to this statement, I went to the archive at groups.google.c om
and see that you have posted to about a hundred unique message threads.
Apparently Lyle got the impression that you were relying too heavily on
others; that's not an issue on which I'd care to make a judgement.

Further, if you check some of Lyle's past responses to me you will see

that

they are obnoxiously condescending and abusive. I believe I can honestly

say

that I have always responded to his abusive posts politely and without
getting personal.

I'd also not care to spend time and energy going back and trying to evaluate
who was unkind to whom, or trying to determine whose attitude was
appropriate. I've "crossed words" with Lyle a few times, myself, but I
respect his knowledge and ability far too much to "write him off" because I
thought him "snippy" and I have had, in times past, an even stronger
opinion.

Given your response to Lyle, I thought you may have misunderstood:

-- Lyle's position (he didn't want to see your posts, which is what
"plonk", "killfile", or "twitfilter " does; but he didn't make
an attempt to stop your posting)

-- the necessity for a "statement" in posted code to "protect" it
(it is legally protected without a statement, it is, as a practical
matter, impossible to protect posted code, except perhaps
from being sold by someone else)

and, that an explanation of those, and some specific issues, might be
helpful.

However, it appears that Lyle no longer wants me to post in this forum and
that MVPs such as yourself will be backing him up. Therefore, I will no
longer post in this forum.

It appears to me that you are oversensitive and are overreacting .

I certainly made no objection to your posting in comp.databases. ms-access,
nor did I either endorse or condemn Lyle's statements. Anyone can post;
anyone can respond. I certainly have no power or authority to prevent anyone
from posting -- MVPs have no "authority" in any case... we've just been
recognized by Microsoft for our assistance to users of Microsoft software.
Nor does the fact that I cast one of the official original approving votes
to establish this newsgroup back in 1993 give me any "authority" -- though I
do have a certain interest in the newsgroup being productive.

If someone is abusive, the appropriate action is a polite complaint to the
abusive party's news server -- as abusive posting is almost certainly
against the news server's Acceptable Use Policy or Terms of Service. Have
you tried that route? Be sure to include the offensive post with full USENET
headers. It almost certainly has more chance of success than a "public
pout."

I have no objection to anyone posting via "AccessMonster, " but am unfamiliar
with the advantages, if any, of doing so instead of reading/posting to the
newsgroup directly. I do know that some believe they profit from the pro
bono work of others.

Larry Linson