By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Help with Access query to search multiple tables

P: 1
I have created a database that has a main table with a description of different products. I have 5 other data bases that are linked sheets that have returns for the 5 different prduct groups. I'd like to have a query run and pull in all the products with their respective return. Is there a way to have it search for the same field on multiple sheets for example if you are looking for Product Sam, then it will check Return Sheet 1 2 or 3 to pull it in. I basically need it to look through the multiple tables and pull in the return respective to the product. This way I can have a master list of all products and all returns. I'm a newbie so let me know if I left anything out. This is the SQL:

SELECT AssetDescriptions.Name, AssetDescriptions.SubAssetClass, AssetDescriptions.SubStrategy, AssetDescriptions.Benchmark, AssetDescriptions.VehicleType, AssetDescriptions.ManagerFee, AssetDescriptions.IndividualMinimum, AssetDescriptions.InstitutionalMinimum, ManagerReturns.[3Y Ann ROR] AS [ManagerReturns_3Y Ann ROR], ManagerReturns.[3Y Std Dev] AS [ManagerReturns_3Y Std Dev], ManagerReturns.[3Y Shrp Rto] AS [ManagerReturns_3Y Shrp Rto], ManagerReturns.[3Y Beta] AS [ManagerReturns_3Y Beta], MutualFundReturns.[3Y Ann ROR] AS [MutualFundReturns_3Y Ann ROR], HedgeFundReturns.[3Y Ann ROR] AS [HedgeFundReturns_3Y Ann ROR], HedgeFundReturns.[3Y Std Dev] AS [HedgeFundReturns_3Y Std Dev], ETFReturns.[3Y Ann ROR] AS [ETFReturns_3Y Ann ROR], ETFReturns.[3Y Std Dev] AS [ETFReturns_3Y Std Dev], ETFReturns.[3Y Shrp Rto] AS [ETFReturns_3Y Shrp Rto], ETFReturns.[3Y Beta] AS [ETFReturns_3Y Beta]
FROM ((IndexDes LEFT JOIN IndexReturns ON IndexDes.CANDIDATE_NAME = IndexReturns.CANDIDATE_NAME) RIGHT JOIN (((AssetDescriptions LEFT JOIN HedgeFundReturns ON AssetDescriptions.CANDIDATE_NAME = HedgeFundReturns.CANDIDATE_NAME) LEFT JOIN ManagerReturns ON AssetDescriptions.CANDIDATE_NAME = ManagerReturns.CANDIDATE_NAME) LEFT JOIN MutualFundReturns ON AssetDescriptions.CANDIDATE_NAME = MutualFundReturns.CANDIDATE_NAME) ON IndexDes.Name = AssetDescriptions.Benchmark) LEFT JOIN ETFReturns ON AssetDescriptions.CANDIDATE_NAME = ETFReturns.CANDIDATE_NAME;

This pulls in all my data in different columns. I was wondering if there was a way to pull in respective data into one column since they all have the same column name. So all the data on the AssetDescriptions page shows up and then under the 3Y Ann ROR will have the return in one column if the product is from MutualFundReturns or ETFReturns.
Aug 6 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.