Update/lookup value of one field based upon entry in previous field

#1 (**permalink**) 28-Jul-2006, 08:18 AM

This UPDATE query works fine, after the fact:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11165-update-lookup-value-one-field-based.html

UPDATE authorizations, consumers SET authorizations.fname =
consumers.fname
WHERE authorizations.bsu_no=consumers.bsu_no;

When I enter bsu_no in rows and then close the table and run the UPDATE
query, it's perfect.

What I want is for the update to occur after entering the bsu_no in the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11165
table and dropping focus on the field.

I know the answer's simple, but it's Friday afternoon and my brain is
fried, so any help is greatly appreciated.

Thanks,

Steve

#2 (**permalink**) 28-Jul-2006, 08:18 AM

1. Your query syntax is incorrect because the join type is not specified.
Open the QBE and draw the appropriate join line between the two tables.
2. Tables do not expose events so there is no place to put the code to run
the update query.
3. And last but not least, you shouldn't be doing this in the first place.
It is not necessary to duplicate data. Data should be stored in one and
only one place. When you need data from a related table, create a query
that joins the necessary tables.

wrote in message
news:1149880693.297062.64330@h76g2000cwa.googlegro ups.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11165
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11165
> This UPDATE query works fine, after the fact:
>
> UPDATE authorizations, consumers SET authorizations.fname =
> consumers.fname
> WHERE authorizations.bsu_no=consumers.bsu_no;
>
> When I enter bsu_no in rows and then close the table and run the UPDATE
> query, it's perfect.
>
> What I want is for the update to occur after entering the bsu_no in the
> table and dropping focus on the field.
>
> I know the answer's simple, but it's Friday afternoon and my brain is
> fried, so any help is greatly appreciated.
>
> Thanks,
>
> Steve
>

#3 (**permalink**) 28-Jul-2006, 08:18 AM

1. OK, I did that in the QBE (I wish I could just do this in SQL and
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11165
avoid Access altogether!). The tables were already joined in a
previously-defined relationship, so I didn't think it'd be necessary,
here.

2. O-kay......

3. "Now we see the corruption inherent in the system!" (points for
reference

) I blame Friday. I'm thinking I need to store the names
in the "authorizations" database so I can report on them later but, of
course, all I need is the bsu_no field stored in the main database and
my later reports can lookup and retrieve the names as needed.

So, I'll end up with a main database, a couple of reference databases,
a date-entry form that requires the bsu_no field but simply retrieves
and displays the names; and later reports that also key off the bsu_no
field and only retrieve the names!

Paradigm block removed, now I can enjoy the weekend, thanks Pat!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11165

Pat Hartman(MVP) wrote:
> 1. Your query syntax is incorrect because the join type is not specified.
> Open the QBE and draw the appropriate join line between the two tables.
> 2. Tables do not expose events so there is no place to put the code to run
> the update query.
> 3. And last but not least, you shouldn't be doing this in the first place.
> It is not necessary to duplicate data. Data should be stored in one and
> only one place. When you need data from a related table, create a query
> that joins the necessary tables.
>
> wrote in message
> news:1149880693.297062.64330@h76g2000cwa.googlegro ups.com...
> > This UPDATE query works fine, after the fact:
> >
> > UPDATE authorizations, consumers SET authorizations.fname =
> > consumers.fname
> > WHERE authorizations.bsu_no=consumers.bsu_no;
> >
> > When I enter bsu_no in rows and then close the table and run the UPDATE
> > query, it's perfect.
> >
> > What I want is for the update to occur after entering the bsu_no in the
> > table and dropping focus on the field.
> >
> > I know the answer's simple, but it's Friday afternoon and my brain is
> > fried, so any help is greatly appreciated.
> >
> > Thanks,
> >
> > Steve
> >