Novice Needs Help With Simple Select Queries

[GAtkins]

My experience with Access 2010 is quite limited but I am trying to learn of course. The attached Access 2010 database contains three tables: 1) a Clients table that includes First, Last, and AcctNum, 2) a Securities table that contains SecSymbol, SecName, Coupon, MatDate, and Price, and 3) a Positions table that includes ClientID, SecuritiesID, and Quantity.

The database also includes 3 select queries: 1) tysonQ that shows all records that include the ticker symbol TSN, 2) a notTSNQ that excludes all records that do not include the ticker symbol TSN, and 3) a fullQ that is a table of all securities in the database.

My problem is this: the query that selects records that include the ticker symbol TSN works as I expect. The query that excludes the ticker symbol TSN works mathematically as I expect because it does exclude records with ticker TSN, but it also includes other clients that own other securities in addition to TSN.

What I am trying to accomplish is to develop a query that shows all clients who DO NOT own TSN. When comparing the tysonQ and the notTSNQ, I want to exclude the two portfolios that own TSN (Carla and Nubli) entirely from the notTSNQ. Carla and Nubli are included in the notTSNQ, even though their TSN positions are not, simply because they own other securities that are not TSN. I would like to eliminate the entire Carla and Nubli portfolios from the notTSNQ because they already own TSN. They are showing up in the notTSNQ because they also own other securities.

What I am trying to do in this example is find the portfolios that do not contain a position in the security TSN.



I have tried this a million different ways but am not smart enough to figure it out. Any help would be greatly appreciated. Thanks in advance.

Glenn

[June7]

Consider:

SELECT Clients.First, Clients.Last, Clients.AcctNum, Securities.SecSymbol, Securities.SecName, Positions.Quantity, Securities.Price, [Positions]![Quantity]*[Securities]![Price] AS MktVal
FROM Securities INNER JOIN (Clients INNER JOIN Positions ON Clients.ClientID = Positions.ClientID) ON Securities.SecuritiesID = Positions.SecuritiesID
WHERE (((Securities.SecSymbol)<>"TSN")) AND Clients.ClientID Not In (SELECT ClientID FROM Positions WHERE SecuritiesID=2);

You have lookups with alias in tables. Recommend against that. Review http://access.mvps.org/access/lookupfields.htm

[GAtkins]

That works great on TSN, thank you very much.

However, I am obviously a complete idiot. When I try to modify your query to select <> WMT for example by changing <>"TSN" to <>"WMT" and SecuritiesID=2 to SecuritiesID=1, the query returns a total market value of 105,955 when it should be 118,900.

I am missing something obvious but cannot figure out what it is. Any further help will also be greatly appreciated. At some point in the future I would also like to group your query by client where the total client market value of <>TSN is on one line.

Thanks again for your help.

Glenn

[June7]

The query is working as designed. All Nubli and Grant records are excluded. That leaves 5 records that total 105,955.

[GAtkins]

<Smacks forehead>! Of course it is. Thank you again very much.

How is the best way (books, online, whatever) for me to learn Access SQL syntax such as your query "Not In (SELECT ClientID FROM Positions WHERE SecuritiesID=1)" specifically, and more broadly Access SQL syntax generally for use in Access databases? I have extensive experience in Excel VBA doing Monte Carlo simulations, but no experience in Access 2010 or SQL at all.

Also, how would I approach summing and consolidating each repeating line (3 Grant's and 2 Corbin's) in the notTSN query that you provided into one line for each person and one MktVal total? I don't mind at all doing the work, I just don't even know where to start.

Thanks again.

Glenn

[June7]

Look at http://www.w3schools.com/SQl/default.asp

Use an aggregate Totals (GROUP BY) query or build a report using its Grouping & Sorting features with aggregate calcs in group footer section.

The report will allow display of detail records as well as summary calcs.