Updating a table with specific info from another table

[John T Ingato]

I have a contacts table with name address and such but are missing all phone
numbers in the phone number fields. I have just received an updated
customer list in Excel and have imported into a new table.

Can I run a query that will take the phone number only from the new table
and update the old tables phone number fields from empty to the proper
information?

[Rich P]

Hi John,

In sql Server you can run a Transact sql statement like this:

strSql = "Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum"

to update a table in one shot.

But I was not able to get it to work in Access - it must be a limitation
of Access Jet Sql. If anyone knows how to make this kind of sql
statement work in Access - please share.

The alternative for Access that will work is to use DAO with sql and
loop through your import Table:

-----------------------------------------------
Dim DB As DAO Database, RS As DAO.RecordSet
Dim strsql As String, strPhone As String , intID As Integer

Set DB = CurrentDB
Set RS = DB.OpenRecordset("ImportTable")
Do While Not RS.EOF
strPhone = RS!Phone
intID = RS!IDfield
strSql = "Update mainTbl Set Phone = '" & strPhone _
& "' Where IDnum = " & intID
DoCmd.RunSql strSql
RS.MoveNext
Loop
---------------------------------------------------

This method only updates one row at a time, but it works.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"Rich P" wrote

In sql Server you can run a Transact
sql statement like this:

strSql = "Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum"

to update a table in one shot.

But I was not able to get it to work in Access -
it must be a limitation of Access Jet Sql. If
anyone knows how to make this kind of sql
statement work in Access - please share.

I am amazed at how many people seem to think that if something works in
their favorite software, it should work unaltered in other software --
especially people intelligent enough to create working SQL in a server DB.

In Access, you use the Query Builder: add each of the Tables as a data
source, join them on the common unique identification fields, drag down the
necessary fields, click on Query in the menu, choose Update, then set the
UpdateTo of the telephone number in the original table to be updated to the
telephone number in the new table.

If you really feel compelled to look at the Jet SQL, you can click the
leftmost icon on the toolbar or choose View | SQL from the design view menu
in Query Builder.

Larry Linson
Microsoft Access MVP

[Rich P]

>>
I am amazed at how many people seem to think that if something works in
their favorite software, it should work unaltered in other software --
especially people intelligent enough to create working SQL in a server
DB.
...
Larry Linson
Microsoft Access MVP
<<

Neither you nor anyone else has demonstrated how to make a Tsql
statement like this one

-----------------------------------------------------
Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join ImportTbl t2 on
t1.IDnum = t2.IDnum
------------------------------------------------------

run in Access Jet Sql. A lot tSql statements can be used by Access Jet
Sql (it is all microsoft). But Jet sql is designed for desktop useage
not Enterprise usage, therefore it is not unreasonable to expect
limitations. Otherwise, why have desktop and enterprise systsms? Why
not just one system? Because not everyone needs an enterprise system.
I don't happen to know how much Jet Sql can do that tSql can do. I am
merely asking if anyone knows how to perform a tSql update as above in
one shot in Access to please share. My feeling is that Jet Sql is not
designed to perform such an operation. But I don't know for sure.

>>

I am amazed at how many people seem to think that if something works in
their favorite software, it should work unaltered in other software
<<

-----------------------------------------------
Dim DB As DAO Database, RS As DAO.RecordSet
Dim strsql As String, strPhone As String , intID As Integer

Set DB = CurrentDB
Set RS = DB.OpenRecordset("ImportTable")
Do While Not RS.EOF
strPhone = RS!Phone
intID = RS!IDfield
strSql = "Update mainTbl Set Phone = '" & strPhone _
& "' Where IDnum = " & intID
DoCmd.RunSql strSql
RS.MoveNext
Loop
---------------------------------------------------

This is the it-works-In-Access altered version of the tSql statement
above. Clearly, the tSql statement is much simpler and much more
efficient.

Yes, it is possible to make Access do anything that .Net or C++ can do
if you want to waste your time with tons of API code (which is all
written in C++ - just like Access is and .Net is) but that would be like
using a hand shovel to dig a 1000 cubic foot ditch instead of a steam
shovel (sql Server).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Quintal]

"John T Ingato" <jt******@hotmail.comwrote in
news:12*************@corp.supernews.com:

I have a contacts table with name address and such but are
missing all phone numbers in the phone number fields. I have
just received an updated customer list in Excel and have
imported into a new table.

Can I run a query that will take the phone number only from
the new table and update the old tables phone number fields
from empty to the proper information?

yes.

Backup the database in case something gfoes wrong..

Open the query builder. Add both tables from the list, Join the
field(s) that will identify which phone number in the new table
belongs to which record in the old table by dragging the field
name(s) from the old to the new. This could be an ID number or
name and address.

Double-click the phone number field in the old table. this will
put it into the bottom pane.

Bring up the query popup menu. Change the query type to update
query. A new row in the bottom pane will appear. Under the
oldtable.phonenumber, type [newtable].[phonenumber]

Click on the Exclamation mark to run the query. check if it
worked correctly.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Larry Linson]

"Rich P" wrote

> I am amazed at how many people
seem to think that if something works
in their favorite software, it should
work unaltered in other software --
especially people intelligent enough
to create working SQL in a server
DB.
Larry Linson
Microsoft Access MVP

Neither you nor anyone else has
demonstrated how to make a Tsql
statement like this one

-----------------------------------------------------
Update t1 Set t1.Phone = t2.Phone from
mainTbl t1 Join ImportTbl t2 on
t1.IDnum = t2.IDnum
------------------------------------------------------

But I did explain, in some detail, how to do it "the Access Way" using the
Query Builder, and how to examine the Jet SQL that is created.

You can't make a "TSQL" statement for Jet, because TSQL is Microsoft SQL
Server-specific. And, my nickname for those who write SQL from scratch in
Access, when they can start with the Query Builder is "SQL Masochists" or
"SQL Bigots" (and not entiredly "all in jest"). If you want to write your
own SQL from scratch, just write SQL to match that which is generated when
you follow my instructions for using the Query Builder.

Here's SQL from the Query Builder for tables as you describe:

UPDATE mainTBL INNER JOIN ImportTBL ON mainTBL.IDnum = ImportTBL.IDnum SET
mainTBL.Phone = ImportTBL.Phone;

You'll notice that the aliasing of the tables is unnecessary because the
Query Builder is keeping track of names and doesn't need abbreviations, but
if you feel compelled to try to accomodate human limitations, the following
works just as well (copied the query above, and modified the SQL of the
copy):

UPDATE mainTBL AS t1 INNER JOIN ImportTBL AS t2 ON t1.IDnum=t2.IDnum SET
t1.Phone = t2.Phone;

And, it doesn't look all that much different than your TSQL, now does it?

Now, _Access and I_ have demonstrated to you how to make the Jet SQL that is
equivalent to your TSQL. Do you need additional explanation?

You've made some statements that are unsupported and misleading, but I am
not going to get involved in any Jet versus SQL Server arguments -- they are
complementary, not competing technologies. I use "appropriate technology"
for particular applications and, even when the database is kept in Microsoft
SQL Server or other server database products, generally use Jet and ODBC to
access the Tables. In fact, most of the paying work I have done, over the
years, in Access was Access clients to various server databases.

Larry Linson
Microsoft Access MVP

[Rich P]

Alright. You got me!

UPDATE Table1 INNER JOIN Table2 ON Table1.idNum = Table2.idNum SET
Table1.Phone = [table2].[phone];
This is the Access version of the tSql statement I started out with. It
is basically the same as the tSql statment - just switched around - I
didn't think that Access couldn't handle an update query like that. I
just don't use the Query builder anymore - I guess it still has its
value.

Note: If I didn't think Access was a great product, I would not be
wasting my time in this NG. Oh yeah, I am way used to the taste of crow
:).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:44**********************@news.qwest.net:

Neither you nor anyone else has demonstrated how to make a Tsql
statement like this one

-----------------------------------------------------
Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join ImportTbl
t2 on t1.IDnum = t2.IDnum
------------------------------------------------------

And *you* have not posted a TSQL statement, either, as the above is
plain vanilla SQL, using none of the specifics of the TSQL language.

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

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:kyVAg.148$qw5.106@trnddc06:

Now, _Access and I_ have demonstrated to you how to make the Jet
SQL that is equivalent to your TSQL.

He didn't post any TSQL. He posted plain vanilla SQL that would run
on any database that supports any variety of SQL whatsoever.

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

[David W. Fenton]

Rich P <rp*****@aol.comwrote in
news:44**********************@news.qwest.net:

I
didn't think that Access couldn't handle an update query like
that.

Then you are a complete idiot.

<PLONK>

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

[Rich P]

Thank you for your insults. If you can make this vanilla sql statement
work in access

Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum

then you may call me an idiot. So Access has its own version. Now we
know thanks to Bob Quintal for demonstrating - For which Larry Linson
took the credit.

If you are calling me an idiot because I suggested that Access has
limitations - it does. This is not misleading. It was desinged to have
limitations. How else is Microsoft going to sell its enterprise system
if the desktop system can perform enterprise operations? I just don't
happen to know all the limitations of Access. I know that there is a
limit on concurrent users, a data size limit. That does not mean the
product is inferior. Access does exactly what it was desinged to do -
perform desktop level operations. It is not a service application. It
is a file application.

This is the kind of exchange that makes this forum look like it is for
amateurs. If someone is posting something for personal gain at the
expense of others, then jump on that person. I made a contribution to
the objective of this thread - how to update a table using sql. Bob
Quintal's reply was more on the money for this particular question. If
my method was complete absurdity, then call me an idiot.

My purpose for exchanging information on this forum is to gain knowledge
and stay in practice with Access. Now I know how to write an update sql
statement using Jet sql. All of the Access projects I am working on now
are projects that I am migrating to the .Net platform with a Sql Server
backend and thus I am not doing any development in Access anymore. I
don't know of a query building tool in Sql server like Access. So I
write all my sql from scratch (not counting Sql Server views which are
basically queries - but you can't create a View that performs an action
like update, delete). Thus, I have to write everything from scratch.
Anyway, I don't use the Access Query Builder - but now I see that it is
a real good way to discover Jet sql syntax.

My request to you is to save your insults for someone who is not
adhereing to the rules of the forum - or someone who is just being
disruptive.

Regards,

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"Rich P" wrote

Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum

then you may call me an idiot.

"Idiot" may be too strong a word, but it is Not Very Smart to keep insisting
that what works in one software package must needs work in another.

So Access has its own version.

Actually, it is Jet that "has its own version."

Now we know thanks to Bob Quintal for
demonstrating - For which Larry Linson
took the credit.

As a matter of fact, I had not seen Bob's post when I posted my response to
you. I didn't, and don't, need help in creating Queries with the Query
Builder -- there are a few things that you can't do with Query Builder, but
plain-vanilla update queries are well within its capabilities.

But, you do not have to worry that I will take any credit by responding to
you either sooner or later, in the future, not unless the little yellow
sticky falls off the bezel of my display.

On this one, I'm with David.

<PLONK>

Larry Linson

[Tim Marshall]

Larry Linson wrote:

And, my nickname for those who write SQL from scratch in
Access, when they can start with the Query Builder is "SQL Masochists" or
"SQL Bigots" (and not entiredly "all in jest").

I had an Access/Jet background before I started wth Oracle. While there
may be one, I don't know of an Oracle tool that is as excellently nifty
as the Acess query design interface for writing Jet SQL.

95% of all my work related DB development in Access is against Oracle
and the complexity of the data and tables from which I must draw data is
such that I must use pass through queries (my experience illustrates the
ODBC notes in a presentation you have or used to have on your site,
Larry). Thus I write Oracle SQL constantly and I think many, many
Oracle developers who use any development tool, be it Access or Power
Builder, learn,as I have, to write SQL based on the actual SQL language.

I find when I am doing Jet development (lately for my own personal,
hobby related stuff), I find that I am so comfortable with writing the
SQL language that I find myself doing such when I am writing DAO code.
When I forget stuff (which is easy to do as the Jet ANSI join syntax is
vastly different from the, IMO, much simpler, Oracle theta join
approach), I do go to the query builder.

It really depends on where you come from, and I think I can agree with
the "SQL Masochists" label, given how easy the SQL builder makes things!
"SQL Bigot" is probably appropriate for some and, I'm guessing,
associated with the disdain I've seen some (ignorant) developers hold
for Access/Jet.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Rich P wrote:

So Access has its own version.

Hi Rich,

Technically, you should be saying "Jet has its own version". Or more
accurately, "dialect"

Don't forget while all database vendors strive to meet SQL 3/ANSI 99 (is
there a later standard?) standards, they all have their own variations
and some differences in how they conform. This is usually most evident
in function names and syntax, but also, in many instances, how the SQL
is constructed.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Rich P]

Hi Tim,

Thank you for your explanation. BTW, when I say Access Sql I mean Jet.
I am using the Expressions interchangeably - since Jet is mostly
associated with Access.

Also, for everyone's information, I happen to really appreciate this
forum. I started my Microsoft programming career in this forum 11 years
ago. I have been trying to keep up with the times by learning OOP,
..Net, Sql Server, etc. My goal is to exchange information to gain
information, knowledge, and so forth. I do not or have nor ever
professed to be comepletely knowledgbeable in all aspects of a
subject/technology, i.e. Sql, Access, .net. Thus, I post in this forum.
I did not happen to know how to write a JET sql statement for Updating
using join tables. I admit it. I did not know how to do that even
though I have written way more sophisticated queries in Tsql. And it
turns out that the Access query builder writes this sql with the
greatest of ease such that someone just starting out with Access can
write such a query.

I have no desire to alienate anybody in this group. I read a lot of the
posts and have seen all sorts of disruptive people (I think they are
called trolls), who harrass other people here. I am not one of those
people and don't plan to become one. I may know a little bit more about
one aspect of Access than someone else, but obviously there are people
with way less time in grade with Access that know more about other
fundamental aspects of Access (Jet) than I do - like writing update
queries using Joined tables.

Just so I have it straight, my idea of participating in this forum is to
exchange/discuss information about Access/Jet/programming. Sometimes my
information is on the money, and sometimes it is skewed. If I feel that
my information may be skewed I will state that I don't know for sure if
this is how a thing is.

Quick note on where I come from on my line of thinking: I started
database programming with DBase3+ in the mid 80s (I was just a youngster
at the time). DBase3+ was the rage back then. Then came Rbase which
took Dbase3+ to the next level - pretty good for DOS based systems.
Then came Windows 3.1 and Access - which just blew everything out of the
water in database programming. Access single handedly revolutionized DB
programming. But along comes Sql Server and .Net to take Access to the
next level. Whenever I mention .Net stuff, I am just keeping up with
the times. We have to face the facts of life. Technologies will always
be getting upgraded and completely revamped. It is the nature of the
beast.

Even though .Net and sql Server are overtaking Access in the Enterprise
arena, since desktops will be around for years to come, Access will live
on - unlike DBase (at the desktop level that is). I think DB2 is
exclusively server/mainframe based. I don't believe Access will ever
see any significant competition in the desktop RDBMS market. Access has
already conquered that market. The competition is at the Enterprise
level. I just don't think any company would be foolish enough to try to
create a BETTER RDBMS for desktops.

For a file application, Access is quite vast in its reaches, and I
certainly don't have familiarity with every one of those reaches - and
even some of the fundamental ones. So I exchange information in this
forum in hopes of getting the information that I don't have. For
example, because of this thread I now know how to write a Jet Sql Update
query using Joined tables.

Thank you to all participants for your participation.

Rich

*** Sent via Developersdex http://www.developersdex.com ***