Crosstab Error

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

Charles D Clayton Jr

I am getting an error and I do not understand why. The error is "Jet
does not recognize '[Document Control].Iso' as a valid field name or
expression. I have two queries. The first is a select and it runs
fine. Here is the SQL of it:

SELECT DISTINCT [Document Control].Iso, Right([iso],6) AS Line,
[Document Control].Rev, [Document Control].Sheet
FROM [Document Control]
WHERE ((([Document Control].Rev)=(SELECT last([rev]) from [Document
Control] as X WHERE X.iso = [Document Control].Iso)))
GROUP BY [Document Control].Iso, Right([iso],6), [Document
Control].Rev, [Document Control].Sheet;

It gives me a list of drawings and the latest rev. I then make a
crosstab based on that query which looks like this:

TRANSFORM Count([Question - Query].Iso) AS CountOfIso
SELECT [Question - Query].Line, Count([Question - Query].Iso) AS
[Total Of Iso]
FROM [Question - Query]
GROUP BY [Question - Query].Line
PIVOT [Question - Query].Rev;

This one is supposed to organize everything by the line number and
show me what revisions are in each line number as well as how many
drawings. But it gives me the error about the field "Iso" in the
table "Document Control." I have plenty of other crosstabs in the
Database that work fine as well as other types of queries. I have
tried removing various elements to see if I can discover the problem
but to no avail. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
 
You change the alias of [document control] to X, so you may need to use X
everywhere that you have the table name.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I appreciate your help with the problem. Unfortunately, I seem to be
a little dense today (if not everyday) and I cannot make your solution
work. I tried changing the [document control] to X but that still
gave me an error (albeit a different one). I only have an imperfect
understanding of queries and the alias trick was something I read in
another post (that works well) but I did not know what I was doing
only that it worked.

Thanks for your time,

Charles D Clayton Jr

[MVP] S. Clark said:
You change the alias of [document control] to X, so you may need to use X
everywhere that you have the table name.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Charles D Clayton Jr said:
I am getting an error and I do not understand why. The error is "Jet
does not recognize '[Document Control].Iso' as a valid field name or
expression. I have two queries. The first is a select and it runs
fine. Here is the SQL of it:

SELECT DISTINCT [Document Control].Iso, Right([iso],6) AS Line,
[Document Control].Rev, [Document Control].Sheet
FROM [Document Control]
WHERE ((([Document Control].Rev)=(SELECT last([rev]) from [Document
Control] as X WHERE X.iso = [Document Control].Iso)))
GROUP BY [Document Control].Iso, Right([iso],6), [Document
Control].Rev, [Document Control].Sheet;

It gives me a list of drawings and the latest rev. I then make a
crosstab based on that query which looks like this:

TRANSFORM Count([Question - Query].Iso) AS CountOfIso
SELECT [Question - Query].Line, Count([Question - Query].Iso) AS
[Total Of Iso]
FROM [Question - Query]
GROUP BY [Question - Query].Line
PIVOT [Question - Query].Rev;

This one is supposed to organize everything by the line number and
show me what revisions are in each line number as well as how many
drawings. But it gives me the error about the field "Iso" in the
table "Document Control." I have plenty of other crosstabs in the
Database that work fine as well as other types of queries. I have
tried removing various elements to see if I can discover the problem
but to no avail. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
Click to expand...
Click to expand...
 
Then remove the alias altogether.

Charles D Clayton Jr said:
I appreciate your help with the problem. Unfortunately, I seem to be
a little dense today (if not everyday) and I cannot make your solution
work. I tried changing the [document control] to X but that still
gave me an error (albeit a different one). I only have an imperfect
understanding of queries and the alias trick was something I read in
another post (that works well) but I did not know what I was doing
only that it worked.

Thanks for your time,

Charles D Clayton Jr

"[MVP] S. Clark" <[email protected]> wrote in message
You change the alias of [document control] to X, so you may need to use X
everywhere that you have the table name.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Charles D Clayton Jr said:
I am getting an error and I do not understand why. The error is "Jet
does not recognize '[Document Control].Iso' as a valid field name or
expression. I have two queries. The first is a select and it runs
fine. Here is the SQL of it:

SELECT DISTINCT [Document Control].Iso, Right([iso],6) AS Line,
[Document Control].Rev, [Document Control].Sheet
FROM [Document Control]
WHERE ((([Document Control].Rev)=(SELECT last([rev]) from [Document
Control] as X WHERE X.iso = [Document Control].Iso)))
GROUP BY [Document Control].Iso, Right([iso],6), [Document
Control].Rev, [Document Control].Sheet;

It gives me a list of drawings and the latest rev. I then make a
crosstab based on that query which looks like this:

TRANSFORM Count([Question - Query].Iso) AS CountOfIso
SELECT [Question - Query].Line, Count([Question - Query].Iso) AS
[Total Of Iso]
FROM [Question - Query]
GROUP BY [Question - Query].Line
PIVOT [Question - Query].Rev;

This one is supposed to organize everything by the line number and
show me what revisions are in each line number as well as how many
drawings. But it gives me the error about the field "Iso" in the
table "Document Control." I have plenty of other crosstabs in the
Database that work fine as well as other types of queries. I have
tried removing various elements to see if I can discover the problem
but to no avail. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
Click to expand...
Click to expand...
Click to expand...
 
Back
Top