Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Duplicate Output Alias

  1. #1
    Join Date
    3rd July 2006
    Posts
    33

    Duplicate Output Alias

    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:

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,376

    Re: Duplicate Output Alias

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    3rd July 2006
    Posts
    33

    Re: Duplicate Output Alias

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,376

    Re: Duplicate Output Alias

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. No Alias names in MS-Query!
    By CapMan in forum Excel and/or SQL Help
    Replies: 12
    Last Post: July 13th, 2013, 05:45
  2. duplicate output to seperate worksheets
    By Joecod in forum EXCEL HELP
    Replies: 1
    Last Post: October 14th, 2005, 07:12
  3. Reuse alias in MS SQL select
    By Neeraj in forum Excel and/or SQL Help
    Replies: 9
    Last Post: August 11th, 2005, 15:04
  4. Error: Duplicate Output Destination
    By summer brew in forum Excel and/or Access Help
    Replies: 3
    Last Post: July 9th, 2005, 03:00
  5. changing directory with alias name
    By Frances Parlow in forum EXCEL HELP
    Replies: 4
    Last Post: July 1st, 2005, 03:10

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno