Query Help

[UTLee]

I have limited Access experience and need some help.



I have the following query:

SELECT tbltempPositions.client, tblSecuritiesCategorized.CategoryId, tblcategory.CategoryDescription, Sum([amount]*[categoryPercentage]) AS CatAmount, tblClients.EquityTarget, qryClientsWithHoldings.TotalPortfolio
FROM ((tblcategory RIGHT JOIN (tbltempPositions INNER JOIN tblSecuritiesCategorized ON tbltempPositions.security = tblSecuritiesCategorized.Security) ON tblcategory.CategoryId = tblSecuritiesCategorized.CategoryId) INNER JOIN tblClients ON tbltempPositions.client = tblClients.client) INNER JOIN qryClientsWithHoldings ON tblClients.client = qryClientsWithHoldings.client
GROUP BY tbltempPositions.client, tblSecuritiesCategorized.CategoryId, tblcategory.CategoryDescription, tblClients.EquityTarget, qryClientsWithHoldings.TotalPortfolio;

I'm trying to add some information to the query from a table that isn't referenced yet in the query: tblModelTargets. Specifically, I need to add the field tblModelTargets.AssetClassTarget but every time I do this in design view, my query results go from around 4,000 records to upwards of 50,000 records. I can't figure out how to add this table correctly.

Here is the resulting SQL when I add tblModelTargets to the query:

SELECT tbltempPositions.client, tblSecuritiesCategorized.CategoryId, tblcategory.CategoryDescription, Sum([amount]*[categoryPercentage]) AS CatAmount, tblClients.EquityTarget, qryClientsWithHoldings.TotalPortfolio, tblModelTargetsNew.AssetClassTarget
FROM (((tblcategory RIGHT JOIN (tbltempPositions INNER JOIN tblSecuritiesCategorized ON tbltempPositions.security = tblSecuritiesCategorized.Security) ON tblcategory.CategoryId = tblSecuritiesCategorized.CategoryId) INNER JOIN tblClients ON tbltempPositions.client = tblClients.client) INNER JOIN qryClientsWithHoldings ON tblClients.client = qryClientsWithHoldings.client) LEFT JOIN tblModelTargetsNew ON tblClients.EquityTarget = tblModelTargetsNew.EquityTarget
GROUP BY tbltempPositions.client, tblSecuritiesCategorized.CategoryId, tblcategory.CategoryDescription, tblClients.EquityTarget, qryClientsWithHoldings.TotalPortfolio, tblModelTargetsNew.AssetClassTarget;

The other fields in the table tblModelTargets are EquityTarget, CategoryID, and AssetClassTarget. Any help would be much appreciated.

[rpeare]

Whatever field you are linking through is creating the multiple records.

So, you are linking from tblClients.EquityTarget to tblModelTargetsNew.EquityTarget. in your table tblModelTargetsNew you have MULTIPLE records that are matching any given EQUITYTARGET in your table tblClients. So you are either linking through an incorrect field (i.e. you should be linking by the primary key of your table tblModelTargetsNew, not through a value, or through a model number or some other field that represents a 1 to 1 relationship.

So let's say you have a EquityTarget in tblClients of 400, in your tblModelTargetsNew what identifies each individual record that has a EquityTarget of 400 from one another, that is likely the field you want to link through in addition to the equity target.

[June7]

Is the table tblModelTargets or tblModelTargetsNew?

I expect this happens because several 1-to-many or many-to-many relationships are involved in the modified query.

Need to understand more about your data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[UTLee]

Sorry, the table name is tblModelTargetsNew. Here's some detail behind the query:

tblClients holds the names of all clients. It also contains a field called EquityTarget which is a number we assign. Each client only has 1 record on this table, and each client only has one EquityTarget that we assign, which can only be 100, 90, 80, 70, 60, 50, 40, 30, 20, 10, or 0.

The table tblTempPositions holds all investment related data for each client (stock and bond holdings). Each client can potentially have many different investment positions. So there are numerous records on this table for each client.

tblModelTargetsNew contains portfolio model details. It has the different percentages for different portfolio models. For example, if a client has been assigned equity target of 100, asset class A, B, C, etc. should be 20% of total portfolio value, 25%, and 55% respectively. The asset classes are defined on this table using the CategoryID field. Also, Each Equity Target has different %s for those various asset classes.

So the query takes all of each clients investment positions and sums them by the asset class. The next thing I'm trying to do is to pull the target %s for each asset class that the client has (which is dependent on the EquityTarget they have been assigned) from the tblModelTargetsNew and display them on this same query.

Does this help?

[June7]

tblTempPositions has many records related to tblClients

then you join tblModelTargetsNew to tblClients and tblModelTargetsNew also has many records in this relationship

Where is table that assigns class to each investment instrument? If investment is Microsoft, what class is Microsoft? Or is Microsoft class different for each client?

Shouldn't the link be tblModelTargetsNew to tblTempPositions?

[UTLee]

tblSecuritiesCategorized assigns each invesment security a categoryID. tblCategories contains this same CategoryID and also the CategoryDescription (i.e. Large Cap, Mid cap, etc.)

Most investments are 100% assigned to only one category but there are a few that I have categorized as 50% Large Cap stocks and 50% short term bonds for example.

[June7]

Afraid I would have to review data. If you want to sanitize db and provide for analysis, will look at.

[rpeare]

like june I'd like to see a sample but I suspect you will have the results you want by linking your clients to your targets using not only the EquityTarget but the portfoliomodelID as well (or whatever field you're using to indicate what type of portfolio they are using).

[UTLee]

Thanks for your help. I got the query working, here's what finally worked:

SELECT qryClientEquityDrift.client, qryClientEquityDrift.EquityTarget, qryClientEquityDrift.CategoryId, qryClientEquityDrift.CategoryDescription, qryClientEquityDrift.CatAmount, tblModelTargetsNew.AssetClassTargetFROM qryClientEquityDrift INNER JOIN tblModelTargetsNew ON (qryClientEquityDrift.EquityTarget = tblModelTargetsNew.EquityTarget) AND (qryClientEquityDrift.CategoryId = tblModelTargetsNew.CategoryID)
ORDER BY qryClientEquityDrift.client, qryClientEquityDrift.CategoryId;

So this is showing me, for every client, the sum (dollar amount) of securities that they currently own categorized by asset class. It takes the securities they have and sums them according to what asset class the securities belong.

However, not all clients have investments in every single asset class. There are 14 different asset classes and some clients only have exposure to, say, 4-8 of them. I'd like to take this a step further by having the query show what it is showing already, the dollar amounts of the asset classes they currently have, plus the other asset classes available that they don't have exposure to (with either a blank or 0 for those).

[June7]

Would probably have to join that query to a dataset of all possible investment/class/client combinations with a join type "Include all records from investment/class/client and only those from ...".

That dataset can be created with a query that includes investment/class/client tables without joins. This will invoke a Cartesian relationship. Every record in each table will associate with every record in the other table(s) so every possible combination is presented. I don't have call to use this type of query so not sure how well they perform.