The Microsoft Jet Engine does not recognize a column in a Query

[maccten2000]

Hi,

I have 2 queries, One which gives me three columns of data and a second one which inserts nulls based on an "iif" statement and giving me a new table.

The SQL for the first table is

SELECT DISTINCT C1.customer, C2.attributeFROM myTable AS C1 INNER JOIN myTable As C2    ON C1.Customer <> C2.Customer Where C2.Attribute NOT IN (Select C3.Attribute From myTable C3  Where C3.Customer = C1.Customer)

The SQL for the second Table is

 SELECT DISTINCT C1.customer, Switch(Flag="Y", attribute, Flag = "N", "No") AS attribute FROM myQuery1;

This in turn then gives me a table which i wish to make a cross tab from. The two queries above run fine but its when I create the cross tab that i get the following error

"The Microsoft Jet Engine does not recognize "C1.customer" as a valid field name or expression

I have tried taking out aliases and replacing them with table names but that doesnt seem to be effective.
I have tried to delete the queries and restart them, I have changed the column headers but alas none of this seems to work.

Does anyone have any ideas

Thanks very much for your time

Cheers

 

[r937]

if myQuery1 is the first query, none of the table prefixes is available when you run a query against myQuery1

it is as though myQuery1 creates a table, and none of the columns in that table has a prefix other than what you assign in the second query...

SELECT DISTINCT customer, ... FROM myQuery1

SELECT DISTINCT myQuery1.customer, ... FROM myQuery1

SELECT DISTINCT C1.customer, ... FROM myQuery1 AS C1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon