Duplicate Output Alias

[daveday]

Hi,

I am having problems with editing a cross tab query. I need to add another column into the cross tab and the column heading is in the data that i am using. I have looked at the SQL code and it is as follows:

TRANSFORM Count(Tbl_BASIS_STRS_RPRT.INUMBR) As [# of OOS] 
SELECT Date() As [Date], Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM, Count(Tbl_BASIS_STRS_RPRT.INUMBR) As [# of OOS] 
FROM Tbl_BASIS_STRS_RPRT LEFT JOIN [Store Table] ON Tbl_BASIS_STRS_RPRT.ISTORE = [Store Table].STRNUM 
WHERE (((Tbl_BASIS_STRS_RPRT.[OOS Stores w Sls])>0)) 
GROUP BY Date(), Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM 
ORDER BY Tbl_BASIS_STRS_RPRT.ISTORE 
PIVOT Tbl_BASIS_STRS_RPRT.E3SPCD In ("DAV","MKS","DSD"); 

The extra colum heading i am trying to add is "WTP" but when i add that in after the DSD then it comes up with the error meesage:

"Duplicate output alias '# of oos'

Thanks for any help in advance.

[Craig Ottley]

Give this a Shot

TRANSFORM Count(Tbl_BASIS_STRS_RPRT.INUMBR) As [# of OOS]
SELECT Date() As [Date], Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM
FROM Tbl_BASIS_STRS_RPRT LEFT JOIN [Store Table] ON Tbl_BASIS_STRS_RPRT.ISTORE = [Store Table].STRNUM
WHERE (((Tbl_BASIS_STRS_RPRT.[OOS Stores w Sls])>0))
GROUP BY Date(), Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM
ORDER BY Tbl_BASIS_STRS_RPRT.ISTORE
PIVOT Tbl_BASIS_STRS_RPRT.E3SPCD In ("DAV","MKS","DSD");

I've taken out Count(Tbl_BASIS_STRS_RPRT.INUMBR) As [# of OOS] From SELECT as this is what was causing the Duplicate Alias

Think of the CrossTab Query Like this...

The TRANSFORM syntax is your Values.
SELECT is your Row Headings (Fields)
PIVOT is your Column Headings

[daveday]

Excellent. That works a treat!

How come it was working as it was with the As [# of OOS] in the SELECT statement without adding the extra column heading?

Its working fine now, just trying to get the understanding of it as well as the solution.

Thanks again

Dave

[Craig Ottley]

Because it's a Cross tab query you transform any Sum, Count, Avg etc as a value. In a normal SELECT statement you can just add Count(Fields)...

z