Help fixing append query

[jazminecat]

I am leaning up the tables in a db, and need to add the fk from one table to 299 records in another table. I'm working with a copy until i get it right.

Currently the year is hardcoded into each of the 299 records. i created a year table with YearID and Year, and added a field to the Copy of my BidsTable to hold the Year ID. I'm trying to append tblYear.YearID to CopyofBidsTable.YearID where tblYear.Year = CopyofBidsTable.Year

Once that's done I can create the relationship, delete the CopyofBidsTable.Year and be one step closer to normalised without evil lookup fields. here's what I have:


INSERT INTO CopyofBidsTable ( YearID )
SELECT tblYear.YearID
FROM tblYear INNER JOIN CopyofBidsTable ON tblYear.Year = CopyofBidsTable.Year
WHERE ((([CopyofBidsTable]![Year])=[tblYear]![Year]));

but it doesn't add the info to the CopyofBidsTable.

Any thoughts?

thanks guys.

 

[PHV]

You wanted this ?
UPDATE CopyofBidsTable AS C INNER JOIN tblYear AS Y ON C.Year = Y.Year
SET C.YearID = Y.YearID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

Well, that got the data in there, but it also created a whole bunch of extra records. no worries, easy enough to delete, just want to understand why. thank you again for your help! That finished up a nasty little chore for me right quick!

 

[PHV]

How an UPDATE query may CREATE a whole bunch of extra records ????