Solved

SQL Code to Combine Two Tables

Posted on 2011-03-08
7
683 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 

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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

687 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