Crosstab Query with "EXISTS" statement

[Parsolamew]

As a solution to my last question, I've begun using EXISTS statements within queries for the data extraction I'm refining. All seemed well, everything worked as intended, but now whenever I try and put that data into a crosstab query for viewing, I get an error message like:

"The Microsoft Office Access database engine does not recognize 'Table.[Column Name]' as a valid field name or expression."

This only happens on queries which contain an EXISTS statement; a few of the simpler extractions don't need one and they form crosstabs just fine. Is there some issue with using this statement within a crosstab, or am I just doing it wrong?

 

[liddlem]

Hi Parsolamew
I have found that it often helps to create a normal select query, and then create a separate cross table query, based on the select on.
Are you trying to do both in one step?

what version of Access are you using?

 

[Banana]

To see the problem, we'd need to see the SQL.

 

[Parsolamew]

Hi Parsolamew
I have found that it often helps to create a normal select query, and then create a separate cross table query, based on the select on.
Are you trying to do both in one step?

what version of Access are you using?

That's what I've done. The normal select query works just fine, but creating a crosstab from that select as a second query is where things choke fatally. I've done the same thing on a few other queries (which do not contain EXISTS statements) and their subsequent crosstabs work fine. For reference, the select query runs as follows:

SELECT anal.SampleID, anal.[Sample Name], sid.[Location ID], sid.[Date Collected], anal.Analyte & "_" & anal.Matrix AS Expr1, loc.[GW Zones], anal.Analyte, anal.Matrix, anal.ResultReport
FROM MAIN_Location AS loc INNER JOIN (MAIN_SampleID AS sid INNER JOIN (LOOKUP_Analyte AS lyte INNER JOIN MAIN_Analytical as anal ON lyte.Analyte = anal.Analyte) ON sid.SampleID = anal.SampleID) ON loc.LocationID = sid.[Location ID]
WHERE (((lyte.Analyte IN ("Acetone", "Bromoform", "cis-1,2-Dichloroethene", "para-Isopropyl Toluene", "Tetrachloroethene", "Trichloroethene", "Diesel C10-C24", "Motor Oil C24-C36") OR (lyte.Analyte IN ("Arsenic", "Chromium", "Nickel") AND anal.Matrix = "Filtrate")))
AND sid.[Sampling Event] = "1Q 2011 GW Monitoring EM"
AND EXISTS (SELECT 1 FROM MAIN_SampleID AS s2 WHERE
s2.[Location ID] = sid.[Location ID] AND
s2.[Sampling Event] = "1Q 2011 GW Monitoring EM" AND
s2.[Sample Type] = "FD"));

This query runs fine, and selects exactly the data I need; no trouble there.

The crosstab query is a simple wizard-guided crosstab using Access, off the original query ("EM Table 8" in this case):


TRANSFORM First([EM Table 8].[Analyte]) AS FirstOfAnalyte
SELECT [EM Table 8].[SampleID], [EM Table 8].[Sample Name], [EM Table 8].[Location ID]
FROM [EM Table 8]
GROUP BY [EM Table 8].[SampleID], [EM Table 8].[Sample Name], [EM Table 8].[Location ID]
PIVOT [EM Table 8].[Expr1];

Attempting to run this query gets the following error:
"The Microsoft Office Access database engine does not recognize 'Main_SampleID.[Location ID]' as a valid field name or expression."

Given that it seems to recognize it just fine in the original select query, I'm a bit puzzled.

Oh, running Access 2007 on Win7 Pro (32bit)