Solved

SQL Code to Combine Two Tables

Posted on 2011-03-08
7
679 Views
Last Modified: 2012-05-11
Hi All,

I'm trying to combine two MS Access tables into a single table within a DoCmd.  here is what I have:

 DoCmd.RunSQL "SELECT " & AxysCodestr & ".*, " & Modelstr & ".* INTO xyz FROM " & AxysCodestr & " FULL JOIN " & Modelstr & " ON (" & AxysCodestr & ".Symbol = " & Modelstr & ".Symbol) WHERE " & AxysCodestr & ".Symbol IS NULL OR " & Modelstr & ".Symbol IS NULL"

I know it looks ugly - it's because the 'strings' AxysCodestr and 'Modelstr' may change. In my example they are not changing, but I wanted to be as close to what I'm trying to accomplish as possible.

Eveytime my code executes the above line - I get a 'Syntax Error in FROM clause'.

Does anyone see anything blatanly wrong?

Thanks!

0
Comment
Question by:Tom_wbi
7 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35071955
can you do debug.print on the entire string and paste it here? it'll be easier to find any bugs that way
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35072028
write AS instead of FROM
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 35072336
Try this ...


 DoCmd.RunSQL "'SELECT ' & AxysCodestr & '.*, ' & Modelstr & '.* INTO xyz FROM ' & AxysCodestr & ' FULL JOIN ' & Modelstr & ' ON (' & AxysCodestr & '.Symbol = ' & Modelstr & '.Symbol) WHERE ' & AxysCodestr & '.Symbol IS NULL OR ' & Modelstr & '.Symbol IS NULL'"


ET
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Tom_wbi
ID: 35072432
Hey All,

It's not AS and the single quotes don't make it any better...I will try a Debug.print and paste it here shortly.
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 35072872
Ok, this should correct your problem ....

 DoCmd.RunSQL "SELECT " & AxysCodestr & ".*, " & Modelstr & ".* INTO xyz FROM " & AxysCodestr & " INNER JOIN " & Modelstr & " ON (" & AxysCodestr & ".Symbol = " & Modelstr & ".Symbol) WHERE " & AxysCodestr & ".Symbol IS NULL OR " & Modelstr & ".Symbol IS NULL"


ET
0
 

Author Comment

by:Tom_wbi
ID: 35072982
etsherman...

OK p here is a dumb question from me:

This code DEFINITELY got rid of my Syntax Error, but my destination table 'xyz' is totall empty!!!  I was expecting (perhaps erroneously) that EVERYTHING from both of my tables would wind up in Table xyz????
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 35073543
Tom ... the answer to your question is you are doing a Inner Join on the Symbol field from both tables ...  meaning only include rows where the join field from both tables are equal.  Therefore, you will never have a NULL Symbol field in your results.

Just open another question and explain in detail what you are trying to accomplish.  

ET
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

914 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

13 Experts available now in Live!

Get 1:1 Help Now