?
Solved

How best to join two SQL queries

Posted on 2011-04-29
5
Medium Priority
?
266 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:billelev
5 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 35495766
You use either * or fld1, fld3, fld7, etc., FROM...
0
 

Author Comment

by:billelev
ID: 35495897
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;
0
 
LVL 7

Expert Comment

by:shambalad
ID: 35496223
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

0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35496361
SELECT * FROM

(SELECT 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;
0
 
LVL 16

Expert Comment

by:Sheils
ID: 35501659
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
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question