Help with "overlap" query

[Darrick]

Hello,

I need someone experienced with SQL and Access to help me with a query. I currently have 5 tables: one for each of 4 different investment companies (Elliot, Eton Park, Karsch, and Greenlight), and one table called "Holdings Overlap" which gives a full list of all of these companies' current positions (stocks that they own).

What I want to do is create a query that gives me the full list of positions in the left most column, and then has four other fields (one for each company) displaying the number of times that position appears in that company's portfolio. This number could range from 0 (if they do not own the stock at all) to 3 (if they own stock and other options on the same thing). Ultimately this will help me by allowing me to see the "overlap" in holdings between these 4 companies.

Here is the SQL code I first tried to use to accomplish this, however it fails to terminate so I assume I am doing something wrong...

SELECT [Holdings Overlap].[Company Name], Count([Elliott Summary].[NAME OF ISSUER]) AS [CountOfNAME OF ISSUER], Count([Eton Park Summary].[NAME OF ISSUER]) AS [CountOfNAME OF ISSUER1], Count([Greenlight Summary].[NAME OF ISSUER]) AS [CountOfNAME OF ISSUER2], Count([Karsch Summary].[NAME OF ISSUER]) AS [CountOfNAME OF ISSUER3]

FROM [Holdings Overlap], [Elliott Summary], [Eton Park Summary], [Greenlight Summary], [Karsch Summary]

GROUP BY [Holdings Overlap].[Company Name]

HAVING (((Count([Elliott Summary].[NAME OF ISSUER]))>1) AND ((Count([Eton Park Summary].[NAME OF ISSUER]))>1) AND ((Count([Greenlight Summary].[NAME OF ISSUER]))>1) AND ((Count([Karsch Summary].[NAME OF ISSUER]))>1));

Please note that "Company Name" and "NAME IF ISSUER" both contain the list of stocks. I apologize for the confusing field names. The actual investment companies are called Eton Park, Karsch, Greenlight, and Elliott.

I feel like this should be a relatively easy query to run in Access. Could anyone please give me some advice for how to create it? Thanks!

[jzwp11]

Welcome to the forum!

I currently have 5 tables: one for each of 4 different investment companies (Elliot, Eton Park, Karsch, and Greenlight),...

First and most importantly, having a table for each investment company is not correct; it violates normalization rules. Like data should be in 1 table.

With the correct structure, you would need a query that groups by investment company and then you would use that query as the recordsource for a cross-tab query in order to display the data in format you want.

[Darrick]

I am not sure I am familiar with the "normalization" rules in this context. What would you suggest?

The main reason I have 4 separate tables is because each company has a list of all of its own holdings. We use these lists to track the performance of each company separately. I could probably consolidate these into one single table though, and maybe have a separate field for the company which owns each stock. Is this what you mean?

[jzwp11]

You might want to take a look at this site that explains normalization from a high level view.

because each company has a list of all of its own holdings

A company has many holdings-this describes a one(company)-to-many(holdings) relationship. That would be structured as follows

tblCompany (holds your 4 investment companies, 1 record for each)
-pkCompanyID primary key, autonumber
-txtCompanyName
(other fields that describe the company)

tblCompanyHoldings
-pkCompHoldID primary key, autonumber
-fkCompanyID foreign key to tblCompany (long number integer datatype field)
-txtHoldingName
(other fields that describe the holding)