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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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:
Upvote 0

Forum statistics

Threads
1,224,455
Messages
6,178,768
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top