Prev: Running a database without ms access
Next: Help with Update Query
From: Chuck W on 19 Apr 2010 11:10
Hi,
I have a table called tblLocations that has fields such as LocationID
(autonumber) and UnitID (number). I also have a table called tblMonths1 that
has LocationID (number). I just created a field called UnitID. I want to
run an update query that will match the LocationIDs from the two tables and
then fill in the values for the newly created UnitID field in the tblMonths1
from tblLocations. Can someone help?

Thanks,

Chuck
From: Daryl S on 19 Apr 2010 11:47
Chuck -

Remember to back up your data before testing any new action queries. This
SQL should work for you (untested):

Update tblMonths1
Set [UnitID] = (Select [UnitID] from tblLocations where
tblLocations.LocationID = tblMonths1.LocationID)

--
Daryl S


"Chuck W" wrote:

> Hi,
> I have a table called tblLocations that has fields such as LocationID
> (autonumber) and UnitID (number). I also have a table called tblMonths1 that
> has LocationID (number). I just created a field called UnitID. I want to
> run an update query that will match the LocationIDs from the two tables and
> then fill in the values for the newly created UnitID field in the tblMonths1
> from tblLocations. Can someone help?
>
> Thanks,
>
From: KARL DEWEY on 19 Apr 2010 11:53
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
There is really no need to duplicate the data UnitID in a second table -
just join tblLocations in each query and you will have it.
After you backup then try this --
UPDATE tblMonths1 INNER JOIN tblLocations ON tblMonths1.LocationID =
tblLocations.[LocationID] SET tblMonths1.UnitID = [LocationID].[UnitID];

--
Build a little, test a little.


"Chuck W" wrote:

> Hi,
> I have a table called tblLocations that has fields such as LocationID
> (autonumber) and UnitID (number). I also have a table called tblMonths1 that
> has LocationID (number). I just created a field called UnitID. I want to
> run an update query that will match the LocationIDs from the two tables and
> then fill in the values for the newly created UnitID field in the tblMonths1
> from tblLocations. Can someone help?
>
> Thanks,
>
 | 
Pages: 1
Prev: Running a database without ms access
Next: Help with Update Query