[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Code to Combine Two Tables

Posted on 2011-03-08
7
Medium Priority
?
686 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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