Link to home
Start Free TrialLog in
Avatar of kabradley

asked on

CrossTab Query Does Not Recognize Valid Field Name

Hello experts!

I have a crosstab query whose recordsource is a query that has two subqueries in it. The Recordsource's sql is as follows:
SELECT tblRegistration.RegistrationName, LPID.LPName, (SELECT Min(Inv.InceptionDate) As InceptionDate FROM Investments As Inv WHERE Inv.RegistrationName = Investments.RegistrationName AND Inv.LPName = LPID.LPID) AS InceptionDate, Investments.DivDate, Sum(Investments.LPUnitsOwned) AS LPUnitsOwned, Year(DivDate) AS [Year], Month(DivDate) AS [Month], (SELECT SUM(Inv.LPUnitsOwned * Fund.LPUnitPrice) AS AmtInvested FROM Investments AS Inv INNER JOIN LPID AS Fund ON Inv.LPName = Fund.LPID WHERE Inv.RegistrationName = Investments.RegistrationName AND Inv.LPName = LPID.LPID AND (Inv.InceptionDate) Is Not Null) AS AmountInvested
FROM tblRegistration INNER JOIN (LPID INNER JOIN Investments ON LPID.LPID = Investments.LPName) ON tblRegistration.RegistrationID = Investments.RegistrationName
WHERE ((Investments.DivDate) > #12/31/1899#)
GROUP BY tblRegistration.RegistrationName, Investments.RegistrationName, LPID.LPName, LPID.LPID, Investments.DivDate, Year(DivDate), Month(DivDate);

And the cross-tab's is:
TRANSFORM Sum(qryDividend.LPUnitsOwned) AS SumOfLPUnitsOwned
SELECT qryDividend.RegistrationName, qryDividend.LPName, qryDividend.Year, Sum(qryDividend.LPUnitsOwned) AS [Total Of LPUnitsOwned]
FROM qryDividend
GROUP BY qryDividend.RegistrationName, qryDividend.LPName, qryDividend.Year
PIVOT qryDividend.Month;

The source query runs fine with no problems, but when it is used as the source for the crosstab query I get the following error:
"The Microsoft Jet Engine Does Not Recognize "Investments.RegistrationName" As a Valid Field name or expression."

I've tried taking the two subqueries out of the source query and running the crosstab and it works fine. The problem is, I need these two subqueries (or more importantly the data they retrieve) as they will be shown in the cross-tab. (I haven't add those fields yet, but they need to be there)

Any ideas on what the problem is?

Thanks in advance!
Avatar of GRayL
Flag of Canada image

I ran across this problem a while back.  I 'discovered' Access will not accept a query as a recordset in a subsequent cross tab query, if the first query contains subqueries.  
Avatar of kabradley


See Ray, that's what I was afraid of, but then in another query I have (this happens to be a union query) there are multiple instance of subqueries and this union query is the recordset for a crosstab what's the difference?

If for whatever reason this can't be resolved, would my only real option be to nest 3 queries and then use the final query as the resulting recordset for the crosstab?
The UNION query produces a unique recordset,  the subquery recordset produces a conditional recordset - best as I can conjure up.  Good to see you back.

Avatar of Jez Walters
Have you thought about using a temporary table to hold the results from qryDividend, and then executing the TRANSFORM query on that instead?

Avatar of Jez Walters
Jez Walters
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think Jez got it.
But, as to the question:  Any ideas on what the problem is?
Thanks Ray,

It's great to have your help again. (although,I'm gonna be honest...I need help when problems arise, and nobody wishes for problems :D)

Anywho, so here's what I tried. I tried copying source query and pasting it in the same query and making it a union query (that returned the same results) and set the subquery fields in the second query of the union query equal to 0. It ran fine, but then when I tried running it from the cross-tab query it threw the same message. So since a psuedo union query won't seems I must take a different route.

The two calculated fields in the original recordset need to be there. So, is my only real option to nest the queries and just have 2 queries feed -> Recordsource -> Crosstab?

That seems like such a waste :/
Didn't refresh my page, so I didn't see Jez's solution. I'll post my results in a few. Thanks guys!
Jez, sorry to be a bother but honestly I've never worked with a query like what you have suggested. All of that sql is to go into one query correct? Where do the semi colons go? Etc,etc. Right now I'm getting a basic syntax in create table error. Which, I'm sure I can resolve I just don't know where to start since this seems more like SQL for MS SQL - etc. then what I'm use to in access.

Karen: (It is Karen?) Those are four separate queries.  They cannot be run together.  
Ha, ok well now I feel pretty dumb. I knew I had done that in MS SQL, but I surely didn't think it was possible in access...come to find out - it isn't! Thanks for the headsup

That did in fact work. I also made the nested queries so that no temp tables were needed to see if there was any difference in performance. Whichever way I go, the crosstab query will become the recordsource for a report. So, on the generate report button I would have to run the "background" queries and then launch the report.

The tiered query method seems the cleaner solution, am I incorrect in thinking this though? Is the temp table a better method?

Thanks guys

And sorry Ray, I think you have mistaken me for someone else :)
Just for the record, you don't actually need the semi-colon at the end of queries - although Access will add one if you save a query without one!

You may have noticed that I simplified your cross-tab query slightly too, to make it a bit easier to read.
I'm not sure if I undertand what you mean by "tiered query method".

Are you suggesting keeping qryDividend as it is, creating a new query that returns all records from qryDividend and then performing the cross-tab on the new query?
I didn't realize that about the semicolons. That's good to know.

What I was referring to to a tiered structure is this:
1.Have a query that retrieves the minimum inception date
2.Have a query that retrieves the amount invested
3.Have qryDividend which no longer uses subqueries to retrieve inception date & amount invested instead query 1 and 2 are now 2 sources of qryDividend.
4.CrossTab query uses qryDividend for its recordsource instead of a temp table.

So essentially instead of making a table using it in the crosstab and deleting it I've made 3 queries instead of the one to feed into the crosstab query. Does that make sense?
Your approach seems to make sense and should overcome your cross-tab problem.  However, bear in mind it might be a bit slow having so many nested queries - in which case you could try using a temporary table as I suggested in ID 21063254 above.

Unfortunately, you'll have to actually try both to see just how fast/slow they are in practice!
That's what I was thinking Jez. I'll just try them and see which is faster. Your suggestions though have been very helpful!
Although like most problems, there are many possible solutions, Jez's solution worked well and definitely did the trick. Thanks Jez!