Subquery query (aahh, what a horrible pun)

[Voltron]

I have a relatively complex query that I have built (it is pulling information for calculated fields from roughly 30 other queries that were created to make calculated fields) and one thing is really causing me problems.

We have clients, but some clients are split into 2 clients (Client and Client - Nevada). We are trying to come up with the appropriate fees, which is partly known and partly calculated. The formula is as follows:

(($1000*4)*(Total Plan Assets/(Total Plan Assets+Total Plan Assets of the second client)))

Total Plan Assets is a calculated field that I have stored in a query.

The pertinent table and query are as follows:

Clients
Client_ID
Consulting_Fee (this is a hard dollar fee, like the $1000 in the formula)
Split (this is a yes or no check box to determine if the client is split into 2)

ClientsWithTotalAssetsQuery
Client_ID (creates the Inner Join)
Total_Assets

To do the formula in one query it seems like a subquery would be the way to go (which vbaInet has also confirmed in a previous thread). However, I am having problems with this subquery. I have done subqueries before, but not to solve a problem quite like this.

It seems like I should have a subquery in the Select section of the base query.

Here is kind of what I was thinking:

SELECT Clients.Client_ID, Clients.Split, Clients.Consulting_Fee, 

(SELECT Clients.Client_ID AS Client_ID_2, ClientsWithTotalAssetsQuery.Total_Assets AS Total_Assets_Client_2 
FROM Clients INNER JOIN [ClientsWithTotalAssets Query] ON Clients.Client_ID = [ClientsWithTotal AssetsQuery].Client_ID 
WHERE Client_ID_2 LIKE *Clients.Client_ID*),

((Clients.Consulting_Fee*4)/(ClientsWithTotalAssetsQuery.Total_Assets/(ClientsWithTotalAssetsQuery.Total_Assets+Total_Assets_Client_2))) AS FeeAmount

FROM Clients INNER JOIN [ClientsWithTotalAssetsQuery] ON Clients.Client_ID = [ClientsWithTotalAssetsQuery].Client_ID

WHERE Clients.Split = Yes

It has been a while since I have written SQL from scratch so this may not be all correct, but this is what I was thinking of. The problem I am having is I am not sure exactly how to execute my WHERE in the subquery properly. I realize that what I have won't work, so is there any way to do what I am trying to do?

Also, is this even anywhere close to something that would be re-workable to be usable?

 

[Galaxiom]

I can see one problem. Like is a string operator and the * need to be concatenated.

WHERE Client_ID_2 LIKE [COLOR=red][B]"*"[/B][/COLOR] [COLOR=red][B]&[/B][/COLOR] Clients.Client_ID [COLOR=red][B]&[/B][/COLOR] [COLOR=red][B]"*"[/B][/COLOR]

Also best you post your code in a code box otherwise it gets unexpected spaces and is harder to read because it wraps on small screens.

 

[Voltron]

I will definitely use the code box from now on. Sorry about that.

Also, with the concatenation of the *'s will this line actually give me what I am looking for? Bringing back the current Client_ID with data before or after it.