Converting QBE Query to SQL string

[beebelbrox]

Good Afternoon

I am trying to convert an Access QBE Query to an SQL string and I am not sure where the error is. This query Joins two tables (Top, Bottom) and makes a New Table "Join_Top_Bottom". The QBE works in acces, here is the Access version of the SQL;

SELECT TOP_050307.obs, TOP_050307.MHSclaim, TOP_050307.line, TOP_050307.CompNo, TOP_050307.Vendor, TOP_050307.PayVendor, TOP_050307.topay, Bottom_050307.ORGClaim, Bottom_050307.PayCode, Bottom_050307.LOB, Bottom_050307.MKTSEG, Bottom_050307.CLientPlanReg, Bottom_050307.RouteCode, Bottom_050307.VTClaim, Bottom_050307.ReportDate INTO Join_Top_Bottom
FROM Bottom_050307 INNER JOIN TOP_050307 ON Bottom_050307.obs = TOP_050307.obs;

I Know that it isnt a direct transpostion from the QBE to SQL but every syntax I've tried wont compile, or I get an error.


Dim strSQL As String

strSQL = SELECT TOP_050307.obs, TOP_050307.MHSclaim, TOP_050307.line, TOP_050307.CompNo, TOP_050307.Vendor, TOP_050307.PayVendor, TOP_050307.topay, Bottom_050307.ORGClaim, Bottom_050307.PayCode, Bottom_050307.LOB, Bottom_050307.MKTSEG, Bottom_050307.CLientPlanReg, Bottom_050307.RouteCode, Bottom_050307.VTClaim, Bottom_050307.ReportDate _
& INTO Join_Top_Bottom _
& FROM Bottom_050307 _
& INNER JOIN TOP_050307 _
& ON Bottom_050307.obs = TOP_050307.obs;

DoCmd.RunSQL strSQL

This bit of code produce the error: Compile Error: Syntax error. If there are any tips someone can pass on I been keen to listen as I need to do this sort of thing quite frequently. I've been picking up a bit of SQl here and there but I havent been able to make the leap from the examples to my specific applications.

Thanks,
Beebelbrox



Any Help would be greatly appreciated

[debasisdas]

What is the use of INTO Join_Top_Bottom in the sql statment ?

[QVeen72]

Hi,

I'am not sure, but String should be enclosed in double quotes (Missing in your post)..
Proper Line Breaks should be given, so that the string is readable..

Check this :

strSQL = "SELECT TOP_050307.obs, TOP_050307.MHSclaim, " _
& " TOP_050307.line, TOP_050307.CompNo, TOP_050307.Vendor, "  _
& " TOP_050307.PayVendor, TOP_050307.topay, " _
& " Bottom_050307.ORGClaim, " _
& " Bottom_050307.PayCode, Bottom_050307.LOB, " _
& " Bottom_050307.MKTSEG, Bottom_050307.CLientPlanReg, " _
& " Bottom_050307.RouteCode, Bottom_050307.VTClaim, " _
& " Bottom_050307.ReportDate " _
& " INTO Join_Top_Bottom " _
& " FROM Bottom_050307 " _
& " INNER JOIN TOP_050307 " _
& " ON Bottom_050307.obs = TOP_050307.obs "
 

Regards
Veena

[beebelbrox]

What is the use of INTO Join_Top_Bottom in the sql statment ?

INTO statement would be used to create the third table "Join_Top_Bottom". This code takes two tables and joins them together in a third table.