Crosstab query Alias error

[maxxev]

I had finished an indepth post on this before my PC crashed I don't have that much time anymore so hopefully this will be enough, please ask any questions and I will try anmd get back to you.

I have a query that works fine, but when I try to put it into a cross-tab it tells me it can't find the fields in the sub-query (the alias'ed table).

Any ideas please?

this is the one that works:Q_LABEL_FORMAT_01

SELECT T_SKUMaster.SKU_ID, T_SKUMaster.SKUCode, (select count(*) from [T_SKU_vs_LabelFormat] where [ID]<[T_SKU_vs_LabelFormat2].[ID] AND [SKU_ID]=[T_SKU_vs_LabelFormat2].[SKU_ID]  ;)+1 AS Rank, T_Labels.FileName, "FORMAT0" & [Rank] & " TITLE" AS FormatTitle, T_Labels.Title, "FORMAT0" & [Rank] & " PATH" AS FormatPath
FROM (T_SKU_vs_LabelFormat AS T_SKU_vs_LabelFormat2 RIGHT JOIN T_SKUMaster ON T_SKU_vs_LabelFormat2.SKU_ID = T_SKUMaster.SKU_ID) LEFT JOIN T_Labels ON T_SKU_vs_LabelFormat2.LabelID = T_Labels.LabelID
ORDER BY T_SKUMaster.SKUCode;

This is the crosstab:

[b]Q_LABEL_FORMAT_02_TITLES[/b]
TRANSFORM First(Q_LABEL_FORMAT_01.Title) AS FirstOfTitle
SELECT Q_LABEL_FORMAT_01.SKU_ID, Q_LABEL_FORMAT_01.SKUCode
FROM Q_LABEL_FORMAT_01
GROUP BY Q_LABEL_FORMAT_01.SKU_ID, Q_LABEL_FORMAT_01.SKUCode
ORDER BY Q_LABEL_FORMAT_01.SKUCode
PIVOT Q_LABEL_FORMAT_01.FormatTitle;

Thank you

 

[genomon]

Have you tried including Q_LABEL_FORMAT_01.Title in the SELECT statement?

Beir bua agus beannacht!

 

[maxxev]

Hi, to clarify the alias'ed fields it cannot find are in the Sub-query of the first query.
e.g. the fields in : T_SKU_vs_LabelFormat2

I get the message "The microsoft access database engine does not recognise '[T_SKU_vs_LabelFormat2].[ID]' as a valid field name or expression
I know it will also fail on '[T_SKU_vs_LabelFormat2].[SKU_ID]' as well through testing.

Cheers

 

[maxxev]

Solved this

SELECT T_SKUMaster.SKU_ID, T_SKUMaster.SKUCode, T_SKU_vs_LabelFormat.SKU_ID, T_SKU_vs_LabelFormat.ID, Count(*) AS RANK, "FORMAT0" & [Rank] & " TITLE" AS FormatTitle, T_Labels.Title, "FORMAT0" & [Rank] & " PATH" AS FormatPath, T_Labels.FileName
FROM T_SKU_vs_LabelFormat AS B, (T_SKU_vs_LabelFormat INNER JOIN T_SKUMaster ON T_SKU_vs_LabelFormat.SKU_ID = T_SKUMaster.SKU_ID) INNER JOIN T_Labels ON T_SKU_vs_LabelFormat.LabelID = T_Labels.LabelID
WHERE (((([T_SKU_vs_LabelFormat].[SKU_ID]=[B].[SKU_ID] And [T_SKU_vs_LabelFormat].[ID]>=[B].[ID]))<>False))
GROUP BY T_SKUMaster.SKU_ID, T_SKUMaster.SKUCode, T_SKU_vs_LabelFormat.SKU_ID, T_SKU_vs_LabelFormat.ID, T_Labels.Title, T_Labels.FileName
ORDER BY T_SKUMaster.SKUCode, Count(*);