Joining Tables on External Data Source with Pivot Tables

[calgary_amy]

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]

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.