Update Query on 2 Tables? Bad Code?

[Megan]

hi everybody-

it seems like my update query should work. the sql view seems logical.
but that might be up to discussion...lol...i'm a newbie!
UPDATE [Case], Issue SET [Case].IssueID = [Issue].[IssueID]
WHERE (([Case].[IssueDesc]=[Issue].[IssueDesc]));
i've got 2 tables, CASE and ISSUE.

.................................................. .............................
CASE

caseid issueid issuedesc
100 0 age
200 0 drugs
300 0 sex
ISSUE
issueid issuedesc
1 age
2 drugs
3 sex
.................................................. .............................

all of the "issueid" fields in CASE are "0."

can i write an update query so that the correct issueid numbers are
associated with the correct issue descriptions in the table, CASE?

so that after the query runs, the CASE table looks like this:

.................................................. .............................
CASE

caseid issueid issuedesc
100 1 age
200 2 drugs
300 3 sex
.................................................. .............................

in design view, i have the CASE and ISSUE tables, and i tried the
query:

UPDATE [Case], Issue SET [Case].IssueID = [Issue].[IssueID]
WHERE (([Case].[IssueDesc]=[Issue].[IssueDesc]));

Field: IssueID
Table: Case
Update To: [Issue].[IssueID]
Criteria: [Case].[IssueDesc]=[Issue].[IssueDesc]

when i clicked "view," it returned one row, caseid, with my 200+
fields i wanted to change from "0" to the correct number. but they all
still had "0."

it seems like it would be an easy enough thing to do, but maybe i've
overlooked something! entirely possible...lol...
RICK COLLARD suggested:

UPDATE Issue RIGHT JOIN Case ON Issue.IssueDesc = Case.IssueDesc SET
Case.IssueID = Issue.IssueID;

THANKS RICK!
i couldn't get it to work though. i'm new with using access and using
aql in access.

thanks everybody,

megan robertson

[John Winterbottom]

"Megan" <me**************@hotmail.com> wrote in message
news:5c*************************@posting.google.co m...

hi everybody-

it seems like my update query should work. the sql view seems logical.
but that might be up to discussion...lol...i'm a newbie!
UPDATE [Case], Issue SET [Case].IssueID = [Issue].[IssueID]
WHERE (([Case].[IssueDesc]=[Issue].[IssueDesc]));
i've got 2 tables, CASE and ISSUE.

.................................................. ...........................
... CASE

caseid issueid issuedesc
100 0 age
200 0 drugs
300 0 sex
ISSUE
issueid issuedesc
1 age
2 drugs
3 sex
.................................................. ...........................
...

update CASE as c inner join ISSUE as i
on c.issuedesc = i.issuedesc
set c.issueid = i.issueid

[Megan]

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2g************@uni-berlin.de>...

"Megan" <me**************@hotmail.com> wrote in message
news:5c*************************@posting.google.co m...

hi everybody-

it seems like my update query should work. the sql view seems logical.
but that might be up to discussion...lol...i'm a newbie!
UPDATE [Case], Issue SET [Case].IssueID = [Issue].[IssueID]
WHERE (([Case].[IssueDesc]=[Issue].[IssueDesc]));
i've got 2 tables, CASE and ISSUE.

.................................................. ..........................
..

CASE

caseid issueid issuedesc
100 0 age
200 0 drugs
300 0 sex
ISSUE
issueid issuedesc
1 age
2 drugs
3 sex

.................................................. ..........................
..

update CASE as c inner join ISSUE as i
on c.issuedesc = i.issuedesc
set c.issueid = i.issueid

Thanks! But I'm unsure of how to use this. I went into Query Design
View, Added the tables, CASE and ISSUE, then changed to SQL View and
entered your code:

update CASE as c inner join ISSUE as i
on c.issuedesc = i.issuedesc
set c.issueid = i.issueid
After I ran it, I still got all "0's" for my "issueid."

In Design View, your code changed my Table Names from CASE to "c" and
ISSUE to "i."

In the bottom part of the Design View window,
Field: IssueID
Table: c
Update To: i.issueid
Criteria: blank

Am I just dense or something and missed your point?

By the way, I'm searching Access Help to see if there's a way to just
run SQL without going into Query Design View.

Thanks for your help! I'll keep trying!

Megan

[John Winterbottom]

"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...

Thanks! But I'm unsure of how to use this. I went into Query Design
View, Added the tables, CASE and ISSUE, then changed to SQL View and
entered your code:
In query design view go straight to sql view and paste the code. But doing
it as above shouldn't affect the result. If it's not working then something
else is going on.

update CASE as c inner join ISSUE as i
on c.issuedesc = i.issuedesc
set c.issueid = i.issueid
After I ran it, I still got all "0's" for my "issueid."

In Design View, your code changed my Table Names from CASE to "c" and
ISSUE to "i."
The tables are aliased - so you don't have to keep typing the table name if
you're writing the sql by hand. Not neccessary though.

In the bottom part of the Design View window,
Field: IssueID
Table: c
Update To: i.issueid
Criteria: blank

try running this code (SQL view)

select * from CASE as c inner join ISSUE as i
on c.issuedesc = i.issuedesc
this will show you which rows will be updated. If it doesn't return any
records means your issuedesc columns do not match up