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.Trad
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....,
raderid) IN ('Trader1', 'Trader2','Trader3')))) as q2
(SELECT q1.ID, q1.TraderID, q1.SubmittedBy, q1.TradeDate, q1.Revenue ETC...) as q1
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
WHERE TraderID IN ('Trader1','Trader2','Trader3') AND Month(TradeDate)=3 AND Year(TradeDate)=2011
ORDER BY TradeDate DESC , ID DESC;
SELECT tbl_rev_group_members.groupid, tbl_rev_group_members.rev_share
WHERE (((tbl_rev_group_members.traderid) IN ('Trader1', 'Trader2', 'Trader3')))