Solved

SQL Code to Combine Two Tables

Posted on 2011-03-08
7
678 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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.
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…

744 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

10 Experts available now in Live!

Get 1:1 Help Now