Solved

Access 2010 Update Query problem

Posted on 2014-09-23
11
230 Views
Last Modified: 2014-09-24
Hi there,

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.

Thanks.
Bill
0
Comment
Question by:boordw
11 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40340584
try like this:
UPDATE SEC INNER JOIN PRC ON SEC.SEC_ID = PRC.PRC_SEC_ID
SET SEC.SEC_PriceCalc = IIF([PRC].[PRC_Price]="", -999999, [PRC].[PRC_Price])
WHERE (([PRC].[PRC_Pricedate] = #03/31/2014#));

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 40340585
The answer is in the type of join you are using. Instead of the INNER JOIN use LEFT JOIN, like this:
UPDATE SEC LEFT 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#));

Open in new window

This will ensure that all records of SEC are affected regardless of the availability of matching PRC record for the day. I also recommend to update a date in the SEC column to indicate which date the prices are current for:
UPDATE SEC LEFT JOIN PRC ON SEC.SEC_ID = PRC.PRC_SEC_ID
SET SEC.SEC_PriceCalc = IIF(IsEmpty([PRC].[PRC_Price]), -999999, [PRC].[PRC_Price]), SEC.SEC_PriceDate = #03/31/2014#
WHERE (([PRC].[PRC_Pricedate] = #03/31/2014#));

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 40340591
After rereading my post I think the actual query should be a bit different:
UPDATE SEC LEFT JOIN (SELECT PRC_SEC_ID, PRC_Price FROM PRC WHERE [PRC].[PRC_Pricedate] = #03/31/2014#) P ON SEC.SEC_ID = P.PRC_SEC_ID
SET SEC.SEC_PriceCalc = IIF(IsEmpty([P].[PRC_Price]), -999999, [P].[PRC_Price]);

Open in new window

Explanation: you need to make sure that your UPDATE statement is not restricted by any WHERE clause that could affect the number of rows to be updated. Thus, you need to use a subquery and a LEFT JOIN
0
 

Author Comment

by:boordw
ID: 40340593
Samo4fun - your solution is invalid (and gives an error) presumably given that PRC_Price is a value.

chaau - your solution is invalid given that Access 2010 doesn't support LEFT JOIN.
0
 
LVL 24

Expert Comment

by:chaau
ID: 40340603
Why do you think Access does not support LEFT JOIN? It is a news for me. I have used Access for many years. Have you tried my query? Has Access given you an error? What error is it.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:boordw
ID: 40340611
I got an error that said that LEFT JOIN was not supported when I tried to run the query.  However, when I used the graphical creation tool (rather than hand-typing the SQL) I did not get the error.  So I must have miss-typed something.  The query does not run.  However, I'm still not getting anything posted when a PRC record does not exist.

(BTW, I was going to deal with the dating description after I got the pricing part working.  For now I would be able to determine where the data had come from by checking if the price was negative.
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40340613
Have you tried my second solution with a sub-query? BTW, I think you need to use IsNull
UPDATE SEC LEFT JOIN 
(SELECT PRC_SEC_ID, PRC_Price FROM PRC WHERE [PRC].[PRC_Pricedate] = #03/31/2014#) P 
ON SEC.SEC_ID = P.PRC_SEC_ID
SET SEC.SEC_PriceCalc = IIF(IsNull([P].[PRC_Price]), -999999, [P].[PRC_Price]);
                                          

Open in new window

Or even simpler:
UPDATE SEC LEFT JOIN 
(SELECT PRC_SEC_ID, PRC_Price FROM PRC WHERE [PRC].[PRC_Pricedate] = #03/31/2014#) P 
ON SEC.SEC_ID = P.PRC_SEC_ID
SET SEC.SEC_PriceCalc = Nz([P].[PRC_Price], -999999);
                                          

Open in new window

0
 

Author Comment

by:boordw
ID: 40340620
Worked like a champ!

So you can just jam in a subquery pretty much anywhere you want?  I guess I need to read about those things.

How do I tell the world that you solved my problem?  I'm new and don't know how this all works.  Also, can you drop me an email at boordw@aol.com or is that not permitted?
0
 
LVL 24

Expert Comment

by:chaau
ID: 40340628
I guess you just need to accept the solution somehow. I have never asked myself, so I am not sure how. I got accepted a lot, so somehow people manage to do so. You can ask any questions here if you wish, or start a new topic if you have additional questions.
0
 

Author Comment

by:boordw
ID: 40340635
OK, I figured out how to accept the solution.  Thanks for the help.

Are you available for remote consulting engagements?  If so, drop me a line at boordw@aol.com.  I'm in California USA.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40341288
You can also go to the users profile (click on their name) .  If they are available for hire, they should have enabled the 'Hire Me' button below their name.  There is also a way to send a private message, without revealing your actual email address on the site.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now