Joining Tables on External Data Source with Pivot Tables

calgary_amy

New Member
Joined
Jul 24, 2008
Messages
24
Hi...I am trying to query data from 4 different tables. I originally tried the code below but it did not work:


Sub SQLQuery1()

Uname = sheetMain.txtUNAME
Pword = sheetMain.txtPWORD

DBASE = "ZET"

Sheets("Bill Determinant").Activate
Range("A3").Select
SQL1 = "BD.CALC_LEVEL, BD.CHARGE_CODE, BD.DATA_SOURCE, BD.DATA_TYPE, BD.DESCRIPTION, BD.INTERVAL_COUNT, BD.NAME, BD.BILL_DETER_FILE_ID, BD.ID, BD.UOM, BDA.NAME, BDA.VALUE, BDD.CHARGE_VALUE, BDD.INTERVAL_TIMESTAMP, BDF.OPR_DATE"
SQL2 = "FROM BILL_DETER_ATTRIBS BDA, FROM BILL_DETER_DATA BDD, BILL_DETER_FILES BDF, BILL_DETER BD"
SQL3 = "WHERE BD.ID = BDD.BILL_DETER_ID AND BD.ID = BDA.BILL_DETER_ID AND BD.BILL_DETER_FILE_ID = BDF.ID AND BDF.DOC_TITLE = BD.BILL_DETER_DOC_TITLE"

ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _
"Select ", SQL1, SQL2, SQL3), _
Connection:=Array("ODBC;DSN=" & DBASE & ";UID=" & Uname & ";PWD=" & Pword & ";DBQ=" & DBASE & ";")

End Sub





So I recorded a macro and got this. Not sure what the & Chr(13) & "" & Chr(10) & is for and when I tried to incorporate it into the macro above it didn't work. Any ideas on how I can get this to work? I'd like to stick to the payout of the first macro (above) since it's cleaner and allows me to add more Where criteria. THANKS!!!

Sub SQLquery2()

Uname = sheetMain.txtUNAME
Pword = sheetMain.txtPWORD

DBASE = "ZET"

Sheets("Bill Deter").Activate
Range("A3").Select

ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _
"SELECT BD.CALC_LEVEL, BD.CHARGE_CODE, BD.DATA_SOURCE, BD.DATA_TYPE, BD.DESCRIPTION, BD.INTERVAL_COUNT, BD.NAME, BD.BILL_DETER_FILE_ID, BD.ID, BD.UOM, BDA" _
, _
".NAME, BDA.VALUE, BDD.CHARGE_VALUE, BDD.INTERVAL_TIMESTAMP, " _
, _
"BDF.OPR_DATE" & Chr(13) & "" & Chr(10) & "FROM BILL_DETER_ATTRIBS BDA, FROM BILL_DETER_DATA BDD, BILL_DETER_FILES BDF, BILL_DETER" _
, _
"MINANTS BD" & Chr(13) & "" & Chr(10) & "WHERE BD.ID = BDD.BILL_DETER_ID AND BD.ID = BDA.BILL_DETER_ID AND BD.BILL_DETER_FILE_ID = BDF.ID AND BDF.DOC_TITLE = BD.BILL_DETER_DOC_" _
, "TITLE"), Connection:=Array("ODBC;DSN=" & DBASE & ";UID=" & Uname & ";PWD=" & Pword & ";DBQ=" & DBASE & ";")


ActiveWorkbook.RefreshAll
Sheets("MAIN").Select

End Sub
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,556
Office Version
2013
Platform
Windows
Just off the bat you need to have spaces at the end of SQL 1 and SQL 2 --

In other words:

a = "spam"
b = "and"
c = "eggs"

d = a & b & c '//Result is "spamandeggs"

a = "spam "
b = "and "
c = "eggs"

d = a & b & c '//Result is "spam and eggs"

Is it possible that's all that's wrong?

---------------
I've never gotten the hang of those crazy Array() syntaxes for these queries...Anyway fyi the Chr(13) is a "carriage return" and Chr(10) is a "line feed" - in other words, like hitting the return key when typing in a text editor.
 
Last edited:

Forum statistics

Threads
1,082,639
Messages
5,366,708
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top