Bit by Action Query Gotcha?

[Ralph]

I am attempting to build an action query in Access 2000 to update a field.

' tblJunky
jTeamID number,
jTeamTag Text(50) <--- new field
..... other fields

'tblTeam
TeamID autonumber
tTeamTag Text(50)

tblTeamJunk once contained only TeamID which contained a foreign key to the
tblTeamLookup table to retrieve the TeamTag.

I would now like to place the TeamTag info into a new field in tblTeamJunk

I have done this a zillion times before - but now while selects work just
fine - when I attempt to create an Action Query - the result is nothing but
nulls.

One attempt:
UPDATE tblTeam INNER JOIN tblJunky ON tblTeam.TeamID = tblJunky.jTeamID SET
tblJunky.jTeamTag = [tblTeam].[tTeamTag]
WHERE (((tblJunky.jTeamID)=[tblTeam].[TeamID]));

I realize I must have run into a 'Gotcha' - some hidden 'feature' I need to
repair - but have run out of things to chew on - any ideas?

-ralph

 

[Ralph]

I am attempting to build an action query in Access 2000 to update a field.

' tblJunky
jTeamID number,
jTeamTag Text(50) <--- new field
.... other fields

'tblTeam
TeamID autonumber
tTeamTag Text(50)

tblTeamJunk once contained only TeamID which contained a foreign key to the
tblTeamLookup table to retrieve the TeamTag.

I would now like to place the TeamTag info into a new field in tblTeamJunk

I have done this a zillion times before - but now while selects work just
fine - when I attempt to create an Action Query - the result is nothing but
nulls.

One attempt:
UPDATE tblTeam INNER JOIN tblJunky ON tblTeam.TeamID = tblJunky.jTeamID SET
tblJunky.jTeamTag = [tblTeam].[tTeamTag]
WHERE (((tblJunky.jTeamID)=[tblTeam].[TeamID]));

I realize I must have run into a 'Gotcha' - some hidden 'feature' I need to
repair - but have run out of things to chew on - any ideas?

-ralph

Found the 'problem'. While nothing was showing up in the datasheet window as
per the docs or normal use, if you run the query it works - it does update
the required fields.

So here is another question - why is it that sometimes you can see and thus
'test' your update query in the datasheet window and why is it you
occasionally can not? Is there some mysterious hidden setting? Some special
Jet SQL? Or is this just another one of those reasons MSAccess is not really
acceptable as a professional tool?

(I have pretty much answered the latter question for myself. <g>)

thanks,
-ralph

 

[Dirk Goldgar]

I am attempting to build an action query in Access 2000 to update a
field.

' tblJunky
jTeamID number,
jTeamTag Text(50) <--- new field
.... other fields

'tblTeam
TeamID autonumber
tTeamTag Text(50)

tblTeamJunk once contained only TeamID which contained a foreign key
to the tblTeamLookup table to retrieve the TeamTag.

I would now like to place the TeamTag info into a new field in
tblTeamJunk

I have done this a zillion times before - but now while selects work
just fine - when I attempt to create an Action Query - the result is
nothing but nulls.

One attempt:
UPDATE tblTeam INNER JOIN tblJunky ON tblTeam.TeamID =
tblJunky.jTeamID SET tblJunky.jTeamTag = [tblTeam].[tTeamTag]
WHERE (((tblJunky.jTeamID)=[tblTeam].[TeamID]));

I realize I must have run into a 'Gotcha' - some hidden 'feature' I
need to repair - but have run out of things to chew on - any ideas?

I don't see any problem with it, although I don't see the point of the
WHERE clause, since you're joining the tables on that equality. It
works fine for me either way, though, in both Access 2000 and 2002. So
either I'm making some false assumption about your tables -- but I
created them just as described -- or there's something odd about your
data, or your query is never being executed. Are you getting any error
message? How are you executing the update query? Via the user
interface, or in code?

 

[Dirk Goldgar]

Found the 'problem'. While nothing was showing up in the datasheet
window as per the docs or normal use, if you run the query it works -
it does update the required fields.

So here is another question - why is it that sometimes you can see
and thus 'test' your update query in the datasheet window and why is
it you occasionally can not?

When you're building an update query, if you change to datasheet view,
you see what is currently in the fields that will be updated, *not* what
will be in them after the query is executed. It is always that way --
what you are doing by switching to datasheet view is running a select
query on the records to be updated.

Is there some mysterious hidden setting? Some special Jet SQL?

No. Try reading the help file topic, "Create an update query". Here's
a quote:

<quote>
To see a list of the records that will be updated, click View on the
toolbar. This list won't show the new values.

Or is this just another one of those reasons
MSAccess is not really acceptable as a professional tool?

Nonsense. Access is a superb professional tool, the best I've ever
heard of for doing what it does, and I've been in this business for 28
years. And obviously, there are a lot people posting in these
newsgroups who use it professionally.

(I have pretty much answered the latter question for myself. <g>)

A professional would learn something about the development tool he was
trying to use before casting aspersions on it.

 

[jchqkjjzhhszhx]

http://www.saob.net/index.php?n=hsdy

 

[Ralph]

When you're building an update query, if you change to datasheet view,
you see what is currently in the fields that will be updated, *not* what
will be in them after the query is executed. It is always that way --
what you are doing by switching to datasheet view is running a select
query on the records to be updated.


No. Try reading the help file topic, "Create an update query". Here's
a quote:

<quote>
To see a list of the records that will be updated, click View on the
toolbar. This list won't show the new values.


Nonsense. Access is a superb professional tool, the best I've ever
heard of for doing what it does, and I've been in this business for 28
years. And obviously, there are a lot people posting in these
newsgroups who use it professionally.


A professional would learn something about the development tool he was
trying to use before casting aspersions on it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for the reply.

I now understand. I am not sure where I got that idea I could see the new 'd
ata' - probably because of the way I was generating update queries for
other databases. Although it would sure have helped if just one source
(MSDN, "Access 2000 Bible", "Mastering Microsoft Access 2000 Development",
or Sybex's two volume set) would have said the rows would be 'blank'. All of
them merely mention that one should view the datasheet to 'test' your query.

Probably because the rest of the action queries update.records - it is felt
that having a long column of 'nulls' is equally useful information for a
'field' update.

Ignore my remarks about the 'professionalism' of MSAccess - I just figured a
little zing would insure a reply.

Thanks again,
-ralph