crosstab query doesnt not recogninze ID Field

[mike02]

I have a Select query which set up all the data i want blended together.. I have a few calculations performed in the query..



Now I want to have a crosstab query, to display the product as a column heading and ClientID, and MonthlyID as the RowHeading, While using CurrentWeight as the Value,

Now, I have never ran into an issue where the ClientID Is not recognized by access. Now, It says tblAccounts.ClientID is not recognized, but tblAccounts is not in the selected query..

Now there is a query in the select query that has tblAccounts.ClientID, in it, but i have not had any issues with that query,

Any Ideas, My search as showed i need to set the parameter for it, however, this does not resolve my issue.

thanks,

[Dal Jeanis]

1) I've found that crosstabs are extremely finnicky, and I've had to rephrase an underlying query to avoid odd little blips like this.
2) Please post the queries, both the crosstab and the underlying ones.

[mike02]

I have got the issue set do being the querys set in the select query, Is there techniques to get around it?

[Dal Jeanis]

Post the queries, so we can look for alternatives and see. Worst case scenario, you can have the underlying query create a temp table and then crosstab on the temp table. But, more likely, we can figure out how to finesse the underlying query so that the crosstab doesn't choke.

[mike02]

Here is the inner query,

SELECT T1.ClientID, T1.ProductID, T1.MonthlyDate, 
(SELECT MAX(T3.MonthlyDate)      
FROM tblPositions AS T3     
WHERE T3.ClientID = T1.ClientID     
AND T3.MonthlyDate < T1.MonthlyDate) AS PriorDate, 

(SELECT SUM(T2.TradePosition)     
FROM tblPositions AS T2     
WHERE T2.ClientID = T1.ClientID     
AND T2.InvestmentID = T1.InvestmentID
AND T2.MonthlyDate <= T1.MonthlyDate) AS NetPosition, 

T1.InvestmentID, tblTradingPrices.AdjClose AS LastMTM, [NetPosition]-[T1].[TradePosition] AS BegPosition, T1.TradePosition AS [Add/Sub Posn], T1.TradePrice AS [Add/Sub Price], T1.BrokerageFee, tblTradingPrices.AdjClose AS MTMPrice, T1.Rate, Abs(Round(IIf([Add/Sub Posn]<0,[Add/Sub Posn]*[Add/Sub Price]*Nz([SECRate],0),0),2)) AS [Fee], [LastMTM]*[BegPosition] AS [Asset Value Beg], [Add/Sub Posn]*[Add/Sub Price]+[BrokerageFee]+[Fee] AS [Assets Add/Sub], ([BegPosition]+[Add/Sub Posn])*[MTMPrice] AS EOMAssetValue, [EOMAssetValue]-[Assets Add/Sub]-[Asset Value Beg] AS [AssetsInc/Dec]

FROM tblTradingPrices 
INNER JOIN tblPositions AS T1 
ON (tblTradingPrices.InvestmentID = T1.InvestmentID) 
AND (tblTradingPrices.MonthlyDate = T1.MonthlyDate)

ORDER BY T1.ClientID, T1.MonthlyDate, T1.InvestmentID;

Here is the Cross Tab:

TRANSFORM First(tblWeightings.Weightings) AS FirstOfWeightings
SELECT tblClients.LastName, tblWeightings.MonthlyDate, qryPositions.BegPosition

FROM ((tblInvestments 
INNER JOIN tblWeightings 
ON tblInvestments.InvestmentID = tblWeightings.InvestmentID) 
INNER JOIN tblTradingPrices 
ON (tblWeightings.MonthlyDate = tblTradingPrices.MonthlyDate) 
AND (tblInvestments.InvestmentID = tblTradingPrices.InvestmentID)) 
INNER JOIN (tblClients INNER JOIN qryPositions 
ON tblClients.ClientID = qryPositions.ClientID) 
ON (qryPositions.InvestmentID = tblWeightings.InvestmentID) 
AND (qryPositions.ProductID = tblWeightings.ProductID) 
AND (qryPositions.MonthlyDate = tblWeightings.MonthlyDate) 
AND (tblInvestments.InvestmentID = qryPositions.InvestmentID)
GROUP BY tblClients.LastName, tblWeightings.MonthlyDate, qryPositions.BegPosition
ORDER BY tblClients.LastName, tblWeightings.MonthlyDate
PIVOT tblInvestments.Investment;

[Dal Jeanis]

Michael - just for grins, and because I've seen this work before, see if you can reformat that crosstab query into a SELECT query that produces records with ONLY the information needed by the crosstab. Then, create a crosstab query that executes the crosstab against that query.

So, the query would produce records that have only (Lastname, MonthlyDate, BegPosition, Investment, Weighting) and the crosstab would look trivial:

TRANSFORM  First(Weighting) AS FirstOfWeightings
SELECT Lastname, MonthlyDate, BegPosition
FROM Myquery
GROUP BY Lastname, MonthlyDate, BegPosition
ORDER BY Lastname, MonthlyDate
PIVOT Investment;

That's my secret weapon when crosstabs go evil on me.

[mike02]

it still gives me the same error.

[Dal Jeanis]

And evil wins another round... Dang.

Okay, again, just for grins, use that query (without the crosstab) to create a temp table, and run the crosstab against the temp table. See if it kills the error.

[Dal Jeanis]

Mike-

I haven't had a chance to review it again myself, but a couple of weeks back I fought my way through PIVOT for someone else. I commented, in passing, that PIVOTs were limited to one table or query. Maybe reviewing that thread would give you some ideas.

https://www.accessforums.net/queries...elp-35644.html

[mike02]

I will check it out, thanks for the help!