Solved

SQL Code to Combine Two Tables

Posted on 2011-03-08
7
681 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
How our DevOps Teams Maximize Uptime

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

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

808 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