Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Query Syntax Error

    SELECT Scmls.ListName,

    (SELECT Scmls.ListName
    ,Count([Scmls.ListName])*2 AS WSO
    ,Sum([Scmls.SalePrice])*2 AS WSODV
    FROM Scmls
    WHERE [Scmls.SellName]=[ListName]),


    On this section below I keep getting a Syntax error but for the life of me I can't figure it out.

    Any ideas , , ,

    (SELECT Scmls.ListName
    ,Count([Scmls.ListName]) AS TSO
    ,Sum([Scmls.SalePrice]) AS TSODV)
    FROM Scmls
    WHERE [Scmls.SellName]<>[ListName]),

    (SELECT Scmls.SellName
    ,Count([Scmls.ListName]) AS WST
    ,Sum([Scmls.SalePrice]) AS WSTDV)
    FROM Scmls
    WHERE [Scmls.ListName]<>[Scmls.SellName]);


    Rick

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I still think this will give you what you're looking for...
    Code:
    SELECT Scmls.ListName, 
       SUM(WSO) AS WSO,
       SUM(WSODV) AS WSODV,
        SUM(TSO) AS TSO,
        SUM(TSODV) AS TSODV,
        SUM(WST) AS WST,
        SUM(WSTTV) AS WSTTV
    FROM (
       SELECT Scmls.ListName 
       ,Count([Scmls.ListName])*2 AS WSO
       ,Sum([Scmls.SalePrice])*2 AS WSODV
       , 0 AS TSO
       ,0 AS TSODV
       ,0 AS WST
       ,0 AS WSTTV
      FROM Scmls
      GROUP BY SCLMS.LISTNAME
      WHERE [Scmls.SellName]=[ListName]
      UNION ALL
      SELECT Scmls.ListName
       0, 0
       ,Count([Scmls.ListName]) AS TSO
       ,Sum([Scmls.SalePrice]) AS TSODV
       ,0,0
       FROM Scmls
       GROUP BY SCMLS.LISTNAME
       WHERE [Scmls.SellName]<>[ListName]
       UNION ALL
       SELECT Scmls.SellName, 0,0,0,0
       ,Count([Scmls.ListName]) AS WST
       ,Sum([Scmls.SalePrice]) AS WSTDV
       FROM Scmls
       GROUP BY SCMLS.SELLNAME
       WHERE [Scmls.ListName]<>[Scmls.SellName]) RS
    GROUP BY ListName
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Okay RNG . . .

    I'm doing monthly reports now but when I'm finished I will look at your suggestion with more vigor.

    It does look interesting.

    I really do appreciate it!

    Thanks . . . Rick

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick,

    I pointed you back to this thread only to see what was the outcome of this suggestion. It would be very good to keep the current discussion in one place. I was not suggesting you come back to this thead for more discussion. The only reason to mention this thread was to not have a duplicate posting. But, because this thread deals directly with where we are in the other thread I thought it best to refer to this thread. There are several people that are watching the other thread so that is the place to continue the discussion.

    Thanks,

Posting Permissions

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