Need help doing a (hopefully) simple calculation using queries.

[Ronald Mcdonald]

I have 10 Tickers that I import from Excel onto Access. Each ticker has about 20-40 securities, each having a # of shares and a price. Shares*Price = Market Value. I was able to make a query (let's call this query B) where I summed the market value of each ticker and the totals seem to match the Excel data. This query only shows the 10 tickers and their aggregate market values. No problems there.

My problem is calculating a cash component for each ticker. The cash component of each ticker can be found by doing NAV - Market Value (total for each ticker). The NAV is NOT part of the Excel file I used to import this data - instead - it's fixed into some larger table that houses over 450 tickers. These are test funds so these NAVs dont change.



I made a new query called C which aims to extract NAV from the large table for each of the 10 tickers and then subtract the market value calculated in query B. Only problem is, instead of getting 10 records, I get nearly 5000! And the numbers really off anyway - not idea where/why it's getting the data for what it's getting.

Any idea on how to proceed? In theory it seems simple, but I just can't wrap my head around. Hopefully my context was explained well enough.

[pbaldy]

What is the SQL of your query? 10*450 is "nearly 5000", which means you may not have the correct join(s) between the tables, which will produce a Cartesian product.