asked on

How best to join two SQL queries

I have a two dynamic queries that I am executing in VBA. I would like to add the rev_share field from the second query on to the first query, joining on TraderID. How best should I go about this?

It seems very complicated. I have saved the two queries in Access, and formed a third query as follows (less fields)

SELECT Query3.ID, Query3.TraderID, Query3.SubmittedBy, Query3.TradeDate, Query3.Revenue, Query2.rev_share
FROM Query2 INNER JOIN Query3 ON Query2.groupid=Query3.TraderID;

but if I extend this to include all fields it will be incredibly long. I can do this, but is there a more simple approach? Or am I stuck with this general format?

SELECT q1.ID, q1.TraderID, q1.SubmittedBy, q1.TradeDate, q1.Revenue ETC....,
q2.rev_share
FROM

(SELECT tbl_rev_group_members.groupid, tbl_rev_group_members.rev_share
FROM tbl_rev_group_members
WHERE (((tbl_rev_group_members.traderid) IN ('Trader1', 'Trader2','Trader3')))) as q2

INNER JOIN

(SELECT q1.ID, q1.TraderID, q1.SubmittedBy, q1.TradeDate, q1.Revenue ETC...) as q1

ON q2.groupid=q1.TraderID;

Query 1

SELECT ID, TraderID, SubmittedBy, TradeDate, StlDate, ClearingSystemName AS ['Clearing Firm'], cust_Name AS ['Customer'], tr_BS AS ['BS'], tr_Qty AS ['Qty'], sec_Symbol AS ['Symbol'], Price, PriceCustomer, comm1_Type AS ['Comm/Markup'], comm1_Value, Revenue
FROM tbl_trades_all
WHERE TraderID IN ('Trader1','Trader2','Trader3') AND Month(TradeDate)=3 AND Year(TradeDate)=2011
ORDER BY TradeDate DESC , ID DESC;

Query 2

SELECT tbl_rev_group_members.groupid, tbl_rev_group_members.rev_share
FROM tbl_rev_group_members
WHERE (((tbl_rev_group_members.traderid) IN ('Trader1', 'Trader2', 'Trader3')))

Open in new window

GRayL

You use either * or fld1, fld3, fld7, etc., FROM...

billelev

ASKER

I see how I could use * in the query defined as q1, but how would I use * in the first line, given I also have to add q2.rev_share. i.e. the first line, as I have it, doesn't seem like it would work.

SELECT *, q2.rev_share
FROM

(SELECT tbl_rev_group_members.groupid, tbl_rev_group_members.rev_share
FROM tbl_rev_group_members
WHERE (((tbl_rev_group_members.traderid) IN ('Trader1', 'Trader2','Trader3')))) as q2

INNER JOIN

(SELECT * FROM tbl_trades_all WHERE traderid IN ('Trader1','Trader2','Trader3')) as q1

ON q2.groupid=q1.TraderID;

shambalad

One alternative:

Dim strTraders As String
Dim strSQL As String
strTraders = "'Trader1','Trader2','Trader3'"
strSQL = "SELECT A.TraderID, B.rev_share, A.SubmittedBy, A.TradeDate, A.StlDate, " & _
        "A.ClearingSystemName AS ['Clearing Firm'], A.cust_Name AS ['Customer'], " & _
        "A.tr_BS AS ['BS'], A.tr_Qty AS ['Qty'], A.sec_Symbol AS ['Symbol'], A.Price, " & _
        "A.PriceCustomer, A.comm1_Type AS ['Comm/Markup'], A.comm1_Value, A.Revenue " & _
        "FROM tbl_trades_all AS A INNER JOIN tbl_rev_group_members AS B " & _
        "ON A.traderid = B.TraderID WHERE A.TraderID In (" & strTraders & ")"

Open in new window

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sheils

If you already have the query in access,do you really need to have it in vba?

Alternatives may be to create the third query (q3) in access then just call the query from vba.

Eg:

If you need a single value from the query

Dim StrValue as string

strValue = DLookup("ValueFieldName","q3","Criteria")

msgbox strValue 'this is just to test that it is returning the desired value'

If you need to loop through q3 then

Function FuncName()

Dim cnn As ADODB.Connection 'remember to select ADODB in your VBa reference'

Set cnn = CurrentProject.Connection

Dim rst As New ADODB.Recordset
rst.ActiveConnection = cnn

Dim strSQL as string

strSQL="Select * From q3"

rst.Open strSQL

While rst.EOF = False

Debug.Print rst.Fields(0)
rst.MoveNext

Wend

End Function