I am trying to write an Update Query in Access 2010. I have two tables -- SEC containing stock and securities information including the last daily price for that security and PRC containing historical pricing information for various securities obtained from an outside pricing source. There is a foreign key link (SEC_ID in SEC and PRC_SEC_ID in PRC) that connects the two tables when needed. Not all securities are priced on a daily basis so there is not a "clean" link between the two tables if I am trying to determine when a particular security in SEC has priced -- meaning on a given day there may not be a record in PRC containing a historical price for the security in question.
In table SEC I have a field called PriceCalc that I would like to have updated in a query based on a given date. Given the range of securities in SEC, if prices exist in PRC for those securities for the given date, I would like [SEC].[PriceCalc] to be updated with [PRC].[PRC_Price] and if there is no price available in PRC for a given security for the given date I would like [SEC].[PriceCalc] to be set to -999,999 so that if anyone tries to utilize the data in any sort of report huge alarms will go off when they look at their reports.
I have tried to following:
UPDATE SEC INNER JOIN PRC ON SEC.SEC_ID = PRC.PRC_SEC_ID
SET SEC.SEC_PriceCalc = IIF(IsEmpty([PRC].[PRC_Price]), -999999, [PRC].[PRC_Price])
WHERE (([PRC].[PRC_Pricedate] = #03/31/2014#));
When I get done I expect to see that my 3,000 record SEC file (which contains securities information from many, many years) would have in field SEC_PriceCalc a combination of -999999's and real prices given that file SEC contains many old, expired securities and bonds that do not price anymore but still need to exist for historical reporting. So for example, a typical daily pricing contains about 600 records in PRC -- so I should see about 2400 securities with -999999 and about 600 securities with real prices.
I don't seem to get a reaction out of "IsEmpty" so that probably is the problem. But I don't know how to programatically determine when PRC does NOT contain valid data for the particular security on the particular date.
I can make this work with two queries -- one that simply updates SEC.SEC_PriceCalc initially with -999,999 and then a second query that updates SEC.SEC_PriceCalc with the 600 found records in PRC so that I wind up with what I want in the SEC.SEC_PriceCalc field, but I need to accomplish this in one query.
I can survive SQL but I am certainly no expert. Any assistance would be appreciated. Also this is my first post here so if I should be posing this question in another manner or location please let me know.