Query Help, Possibly DLookup?

[UTLee]

I have limited experience with Access and need some help with a query. Here’s the situation and I apologize if I’m not clear on certain details:

I have a table called "tblClients" that shows the client’s name and their assigned model portfolio (there are 4 different investment models that a client could be assigned to but each client can only assigned to one model).

I have table called "tblModelTargets" that has the following fields: “Model” (which contains the same model portfolio names from above), “CategoryDescription” which shows asset class names (16 different asset classes), and a field called “Target” which shows my recommended percentage that should be allocated to that asset class.

So, there are 4 different portfolio models and 16 different asset classes so the table "tblModelTargets" has 64 records. The reason for this is that the 4 portfolio models have different target percentages to the asset classes.

Now, I’ve already created a query that shows the client name, assigned portfolio model, $ amounts of each asset class in their portfolio, and the percentage of each asset class $ amount compared to their total portfolio $ amount.

I need the query to look at the clients portfolio model name, then look at the asset class, go to the "tblModelTargets" table and pull the target percent back to the query so I can compare how over/under weight clients’ asset classes are to the target percent. I’ve read about DLookup but can’t figure out how to get it to work, I’ve been trying to use it under the expression builder.

Any help/input is greatly appreciated. If it would help, I can post examples of any of these items.


Lee

[June7]

Every client is associated with one model and each model has 16 classes. So each client always has 16 classes?

Would be nice to have sample data. Create some tables in a post or provide db (follow instructions at bottom of my post).

[Dal Jeanis]

Don't use Dlookup, use a simple join. Assuming roughly this structure:

tblClients
  ClientID    PK
  ModelID     FK to tblModels
tblModels
  ModelID     PK  
  ModelName   text
tblCategories
  CatID       PK
  CatDesc     Text
  CatNotes    Memo
tblModelTargets
  MTKey       Autokey
  ModelID     FK to tblModels
  CatID       FK to tblCategories      
  TargPercent Currency   (to get 4 digits)

And assuming that your existing query gets you something like this:

qryPortfolios
  ClientID    FK to tblClients  
  CatID       FK to tblCategories       
  CatAmount   Currency 
  TotAmount   Currency
  CatPercent  Currency   (to get 4 digits)

You can use this kind of join, which will give you the desired percentage for each:

SELECT
  Q1.ClientID,
  Q1.CatID,
  Q1.CatAmount,
  Q1.TotAmount,
  Q1.CatPercent,
  TM.TargPercent
FROM 
   ((qryPortfolios AS Q1  
    INNER JOIN tblClients AS TC
    ON TC.ClientID = Q1.ClientID)
   INNER JOIN tblModelTargets AS TM
   ON TC.ModelID = TM.ModelID
   AND Q1.CatID = TM.CatID)

Hmmm. Nope. Unfortunately, using that direction for the join leaves a hole where the person has none of any given category which is required by the model.

Hmmm. Okay, go this way:

SELECT
  TC.ClientID,
  TM.CatID,
  TM.TargPercent,
  NZ(Q1.CatAmount) As CatAmount,
  NZ(Q2.TotAmount) As TotAmount,
  NZ(Q1.CatPercent) As CatPercent
FROM 
 ( (  (tblClients AS TC
       LEFT JOIN
          (SELECT ClientID, MAX(TotAmount) AS TotAmount
           FROM qryPortfolios
           GROUP BY ClientID
          ) AS Q2
       ON TC.ClientID = Q2.ClientID
       )
    LEFT JOIN tblModelTargets AS TM
    ON TC.ModelID = TM.ModelID
    )
  LEFT JOIN qryPortfolios AS Q1  
  ON Q1.ClientID = TC.ClientID
  AND Q1.CatID = TM.CatID
  )

[UTLee]

Thanks for your response. Each client doesn't always have to have every asset class. Here's an sample of the two tables and the query:

tblClients
ClientName	PortfolioModel
Client 1	GROWTH PORTFOLIO
Client 2	CASH-FLOW PORTFOLIO
Client 3	CONSERVATIVE PORTFOLIO
Client 4	INCOME PORTFOLIO

tblModelTargets
ID	Model	CategoryDescription	Target
65	CASH-FLOW PORTFOLIO	U.S. LargeCap Equities	10.67%
66	CONSERVATIVE PORTFOLIO	U.S. LargeCap Equities	5.33%
67	GROWTH PORTFOLIO	U.S. LargeCap Equities	16.00%
68	INCOME PORTFOLIO	U.S. LargeCap Equities	0.00%

qryAssetClassDrift
client Model CategoryDescription AssetClassValue TotalPortfolioValue SumOfPercent ModelTarget
Client 1 GROWTH PORTFOLIO U.S. Large Cap Equities $3,514.71 $20,309 17.31%
Client 2 CASH-FLOW PORTFOLIO U.S. Large Cap Equities $55,645.88 $2,403,400 2.32%


I'd like the query to be able to return 16% and 10.67% in the ModelTarget field for the two clients in that query

[UTLee]

Thanks but I'm not sure I understand. Can you see above the sample tables/query I provided and try explain again?

[UTLee]

qryAssetClassDrift
client	Model	CategoryDescription	AssetClassValue	TotalValue	SumOfPercent	ModelTarget
Client 1	GROWTH PORTFOLIO	U.S. Large Cap Equities	$3,514.71	$20,309	17.31%
Client 2	CASH-FLOW PORTFOLIO	U.S. Large Cap Equities	$55,645.88	$2,403,400	2.32%

[June7]

So you don't need to calculate at the asset level, just at the model level?

Should be saving the Model ID value in the Client table.

Create a query that joins the two tables by linking on the model ID fields. This will make the ModelTarget data available for each client record.

[Dal Jeanis]

First, a query called qryAssetClassDrift must exist that returns the values you listed. Get rid of the ModelTarget column from that query. Then, this query will get you what you want:

query qryAssetClassDrift2: 
SELECT
  TC.ClientName,
  TM.CategoryDescription,
  NZ(Q1.AssetClassValue) As AssetClassValue,
  NZ(Q2.TotalPortfolioValue) As TotalPortfolioValue,
  NZ(Q1.SumOfPercent) As SumOfPercent, 
  TM.Target As ModelTarget
FROM 
 ( (  (tblClients AS TC
       LEFT JOIN
          (SELECT ClientName, MAX(TotalPortfolioValue) AS TotalPortfolioValue
           FROM qryAssetClassDrift
           GROUP BY ClientID
          ) AS Q2
       ON TC.ClientName = Q2.ClientName
       )
    LEFT JOIN tblModelTargets AS TM
    ON TC.PortfolioModel = TM.Model
    )
  LEFT JOIN qryAssetClassDrift AS Q1  
  ON Q1.ClientName = TC.ClientName
  AND Q1.CategoryDescription = TM.CategoryDescription
  )
WHERE NZ(Q1.SumOfPercent) > 0 OR TM.Target > 0
ORDER BY TC.ClientName, TM.CategoryDescription;

That will include a line for each AssetClass for each client where he either SHOULD have that asset, or DOES have that asset.

It may be that a more efficient query can be written, but I'd have to see the existing SQL for qryAssetClassDrift in order to determine that.

[UTLee]

"So you don't need to calculate at the asset level, just at the model level?"
There doesn't really need to be any calculation at all. I've already entered all of the target percentages for each Model's asset classes.

Also, the ModelID field is an autonumber column that doesn't really mean anythign. Would there be a better way of setting up this table?

[UTLee]

Is this supposed to be a new query? I created a new query with your code and am getting: Join expression not supported

Here's my original query's sql:

SELECT qryAllocationSheetData.client, qryAllocationSheetData.CategoryDescription, Sum(qryAllocationSheetData.Value) AS AssetClassValue, qryClientsWithHoldings.PortfolioModel, qryClientsWithHoldings.TotalPortfolioValue AS TotalValue, Sum(qryAllocationSheetData.Percent) AS SumOfPercentFROM qryAllocationSheetData INNER JOIN qryClientsWithHoldings ON qryAllocationSheetData.client = qryClientsWithHoldings.client
GROUP BY qryAllocationSheetData.client, qryAllocationSheetData.CategoryDescription, qryClientsWithHoldings.PortfolioModel, qryClientsWithHoldings.TotalPortfolioValue, qryAllocationSheetData.CategoryId;

[Dal Jeanis]

June - UTLee's using the Model Name as a key.

UTLee - Normally, for efficiency, we store the ModelID as the Foreign Key on all the other tables and queries, and just pull in the name from the model table when it's needed for display.

UTLee - Please post the SQL for qryAllocationSheetData and qryClientsWithHoldings. Also, please post the actual layout of the tables. Is "client" the text name of the client? If so, why is it ClientName in the table, and Client in the query?

[UTLee]

Dal Jeanis - here's the sql for the other queries:

qryAllocationSheetData:
SELECT tbltempPositions.client, tbltempPositions.account, tbltempPositions.accountID, tbltempPositions.security, Sum(tbltempPositions.amount) AS SumOfamount, tblSecuritiesCategorized.CategoryId, tblcategory.CategoryDescription, tblSecuritiesCategorized.CategoryPercentage, ([amount]*[CategoryPercentage]) AS [Value], qryClientsWithHoldings.TotalPortfolioValue, ([amount]*[CategoryPercentage])/[TotalPortfolioValue] AS [Percent]
FROM (tblcategory INNER JOIN (tbltempPositions INNER JOIN tblSecuritiesCategorized ON tbltempPositions.security = tblSecuritiesCategorized.Security) ON tblcategory.CategoryId = tblSecuritiesCategorized.CategoryId) INNER JOIN qryClientsWithHoldings ON tbltempPositions.client = qryClientsWithHoldings.client
GROUP BY tbltempPositions.client, tbltempPositions.account, tbltempPositions.accountID, tbltempPositions.security, tblSecuritiesCategorized.CategoryId, tblcategory.CategoryDescription, tblSecuritiesCategorized.CategoryPercentage, ([amount]*[CategoryPercentage]), qryClientsWithHoldings.TotalPortfolioValue, ([amount]*[CategoryPercentage])/[TotalPortfolioValue]
ORDER BY tbltempPositions.client, tbltempPositions.account, tblSecuritiesCategorized.CategoryId;

qryClientsWithHoldings:
SELECT tblClients.client, tblClients.Group, tblClients.Manager, tblClients.PortfolioModel, tblClients.Rebalanced, Format([SumOfamount],"$#,##0;($#,##0)") AS TotalPortfolioValue
FROM qryCurrentClientTotals INNER JOIN (tblClients RIGHT JOIN tblAccounts ON tblClients.client = tblAccounts.client) ON qryCurrentClientTotals.client = tblAccounts.client
GROUP BY tblClients.client, tblClients.Group, tblClients.Manager, tblClients.PortfolioModel, tblClients.Rebalanced, Format([SumOfamount],"$#,##0;($#,##0)")
HAVING (((tblClients.Rebalanced)=True Or (tblClients.Rebalanced)=False) AND ((Format([SumOfamount],"$#,##0;($#,##0)"))>0))
ORDER BY tblClients.client;

Sorry for the plain text, I don't know how to post code like you did earlier. The tblClients field name is actually "client" and not "clientname". Also, this is a database someone else designed and I inherited after that person left our firm. I'm sure there are more efficient ways of creating these queries...


Thanks again for your efforts

[Dal Jeanis]

You post your code with the word code in square brackets [] before it and /code in square brackets [] afterward - like [ /code ] without the spaces - then the forum will maintain your layout with all spaces and such in it. I believe there's a code button as well, where you highlight the desired words and press the button, and it will insert the code tags for you.

[Dal Jeanis]

Okay, starting to feel like it is turtles all the way down...

Please post the code for qryCurrentClientTotals, and any subqueries of that one...

[UTLee]

qryCurrentClientTotals

SELECT tblClients.client, Sum(tbltempPositions.amount) AS SumOfamount, tblClients.specialFee, tblClients.Manager
FROM (tblClients INNER JOIN tblAccounts ON tblClients.client = tblAccounts.client) INNER JOIN tbltempPositions ON (tblAccounts.accountID = tbltempPositions.accountID) AND (tblClients.client = tbltempPositions.client)
GROUP BY tblClients.client, tblClients.specialFee, tblClients.Manager;