Solved

Performing SQL Join and Calculation Simultaneously

Posted on 2011-02-24
5
407 Views
Last Modified: 2012-05-11
Hi All.,

I have two tables (temp1 and temp2) in an MS Access Project that contain 3 fields each: Code, Symbol, Quantity.  My goal is to JOIN these tables on Symbol and select information into a new table, called TableResult but AT THE SAME TIME calculate the SUM of the Quantity field from temp1 and temp2.

I can achieve a simple SELCT w/JOIN as such:
DoCmd.RunSQL "SELECT temp1.Code, temp1.Symbol, temp1.Quantity INTO TableResult FROM temp1 LEFT JOIN temp2 ON temp1.Symbol=temp2.Symbol"

but do NOT know how to modify the above statement to SUM temp1.Quantity + temp2.Quantity and then have ONLY the resultant NET QUANTITY in my table, TableResult.

Thanks!
0
Comment
Question by:Tom_wbi
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34972665
DoCmd.RunSQL "SELECT temp1.Code, temp1.Symbol, temp1.Quantity + temp2.Quantity as Quantity INTO TableResult
                FROM temp1
                LEFT JOIN temp2
                  ON temp1.Symbol=temp2.Symbol"
0
 

Author Comment

by:Tom_wbi
ID: 34973108
Hi Sharath:

I have already tried (and tried again per your suggestion) the following:

DoCmd.RunSQL "SELECT temp1.Code, temp1.Symbol, temp1.Quantity+temp2.Quantity As Quantity INTO TableResult FROM temp1 LEFT JOIN temp2 ON temp1.Symbol=temp2.Symbol"

The resulting Quantity in my TableResult is ALWAYS the Quantity from table temp1 - I can never get the mathematical operation to work. My feeling is that since I am only Selecting From temp1 that the code has no idea what temp2.Quantity is???
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34973157
No, you are not selecting from temp1. You are selecting the records from record set after joining temp1 and temp2. So temp2.Quantity should be considered. Do you have Quantity as NULL or zero in temp2. Can you check the data? Try with INNER JOIN and see if you are still facing any issue for the common data.
Otherwise post some data from your both tables. will check it at my end.
0
 

Author Comment

by:Tom_wbi
ID: 34973178
I noticed I'm storing Quantiy as  text Value - do I need to do some type of conversion, like Cdbl?
0
 

Author Comment

by:Tom_wbi
ID: 34973206
That was it - my own (I won't sai it!)

Here is the resultant code that worked:

DoCmd.RunSQL "SELECT temp1.Code, temp1.Symbol, Cdbl(temp1.Quantity)+Cdbl(temp2.Quantity) As Quantity INTO TableResult FROM temp1 LEFT JOIN temp2 ON temp1.Symbol=temp2.Symbol"

By doing the Cdbl conversion on temp1.Quantity and temp2.Quantity - the mathematical oepration now works. That was my whole problem all along!!!

Thank you - will award points to your answer above for taking the time to try to help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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