Prev: How to use the RunCommand acCmdEncryptDecryptDatabase in 2007
Next: asfasdf
From: SageOne on 4 May 2010 15:48
Hi,

How do I update a field in a table in Access based on a "not null" result of
a join to another table.

For example, I would like a field in table1 to update to "yes" when the join
to table2 is not null. Normally, I would just use a select query with an IFF
function but in this instance I am trying to update a static table.
From: John W. Vinson on 4 May 2010 16:08
On Tue, 4 May 2010 12:48:01 -0700, SageOne <SageOne(a)discussions.microsoft.com>
wrote:

>Hi,
>
>How do I update a field in a table in Access based on a "not null" result of
>a join to another table.
>
>For example, I would like a field in table1 to update to "yes" when the join
>to table2 is not null. Normally, I would just use a select query with an IFF
>function but in this instance I am trying to update a static table.

Create a join in an Update query. No IIF is needed:

UPDATE firsttable INNER JOIN seconttable
ON firsttable.joinfield = secondtable.joinfield
SET somefield = "yes";

will update only those records which have a match.

For a more specific answer please post a more specific question.
--

John W. Vinson [MVP]
From: tasha on 6 May 2010 16:38
Mr. Vinson:

Is it possible to do this also with a field? (See "combo box/relationship
help" for more background info)

"John W. Vinson" wrote:

> On Tue, 4 May 2010 12:48:01 -0700, SageOne <SageOne(a)discussions.microsoft.com>
> wrote:
>
> >Hi,
> >
> >How do I update a field in a table in Access based on a "not null" result of
> >a join to another table.
> >
> >For example, I would like a field in table1 to update to "yes" when the join
> >to table2 is not null. Normally, I would just use a select query with an IFF
> >function but in this instance I am trying to update a static table.
>
> Create a join in an Update query. No IIF is needed:
>
> UPDATE firsttable INNER JOIN seconttable
> ON firsttable.joinfield = secondtable.joinfield
> SET somefield = "yes";
>
> will update only those records which have a match.
>
> For a more specific answer please post a more specific question.
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 6 May 2010 21:31
On Thu, 6 May 2010 13:38:01 -0700, tasha
<tashaw(a)TridentSeafoods.com(donotspam)> wrote:

>Mr. Vinson:
>
>Is it possible to do this also with a field? (See "combo box/relationship
>help" for more background info)

I'm not at all sure what you mean. You can only update a table by updating its
fields. Could you explain (in this thread so I don't have to do a Google
Groups search for the other thread)?
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: How to use the RunCommand acCmdEncryptDecryptDatabase in 2007
Next: asfasdf