I need to look up data in multiple tables, however the field name in each table will vary (all the selected field names are in another table [called tbl_ticker] ). I used DLookup for one table but I’m not sure how to do this for more than one (I assume it has to be done in SQL. Do i use a Union? I'm not sure where to start in SQL).
SELECT tbl_ticker.Ticker, DLookUp([tbl_ticker].[ticker],"[tbl_Sheet01]" ,"date=#" & [Forms]![frm_HistData1]![txt_MaxDate] & "#") AS Price INTO tbl_Returns
GROUP BY tbl_ticker.Ticker
ORDER BY tbl_ticker.Ticker;
**The desired result is to create a table that has the field names in the tbl_ticker table in the first column, and the looked up values in the next column(s).