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

    Unanswered: SQL Query syntax problem

    Hello All . . .

    Here is what I'm trying (Very unsuccessfully of course) to complete.

    Code:
    If C.Ofcname=S.ListName AND S.SellName =  then
    WSODV=Sum(S.SalePrice)*2 (NOT shown in Results Record set)
    WSO=Count(*)S.ListName*2  
    (IS shown in the results record set AS [We Sold Ours])
    
    If C.Ofcname=S.ListName AND NOT S.SellName =  then
    TSODV=Sum(S.SalePrice)  (NOT shown in Results Record set)
    TSO=Count(*)S.ListName 
    (IS shown in Results Record set AS [They Sold Ours])
    
    If C.Ofcname=NOT S.ListName AND IS S.SellName =  then
    WSTDV=Sum(S.SalePrice) (NOT shown in Results Record set)
    WST=Count(*)S.ListName 
    (IS shown in Results Record set AS [They Sold Ours])
    Then I need to do this.
    TotalTransactions=WSO+TSO+WST (IS shown in Results Record set as [Total Transactions])
    ListDV = C.OfcName=S.ListName Sum(L.SalePrice) (IS shown in Results Record set as [List DV])
    TotalDV = WSODV+TSODV+WSTDV (IS shown in Results Record set as [Total DV])

    I hope I’ve explained this well enough.

    Thanks so much. Rick

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    huh?

    Is that the actual SQL statements?

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    No - Because I'm not well versed in VBA or SQL. However, I have been able to piece it together with help.

    Here is what I tweaked . . .
    Code:
    SELECT C.OfcName, Sum(IIf(Left(S.SellName,Len(C.OfcName))=Left(S.ListName,Len(C.OfcName)),S.SalePrice*2,0)) AS WSODV, Sum(IIf(Left(S.SellName,Len(C.OfcName))=Left(S.ListName,Len(C.OfcName)),1,0))*2 AS WSO, Sum(IIf(Left(S.SellName,Len(C.OfcName))=C.OfcName,S.SalePrice,0)) AS TSODV, Sum(IIf(Left(S.SellName,Len(C.OfcName))=C.OfcName,1,0)) AS TSO, Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),S.SalePrice,0)) AS WSTDV, Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),1,0)) AS WST, Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),S.SalePrice)) AS ListDV
    FROM CBAll AS C, SCMLS AS S
    WHERE (((C.OfcName) In (Left([S].[SellName],Len([C].[OfcName])),Left([S].[ListName],Len([C].[OfcName])))))
    GROUP BY C.OfcName;
    This is my current problem . . .

    How do I total WSODV+TSODV+WSTDV, and WSO+TSO+WST and place the results in two feilds at the far right side of the Results Records set.

    How do I place the resuts record set table on the last page of a report once the query is completed? (SubForm?)

    Thanks Much . . . Rick

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I suggest you do this in several queries and utilize the query designer grid in Access instead of trying to create an SQL statement that does it all at once. Fact is, if it is really complex, Access is likely running several queries in steps anyway in the background, so you won't take a performance hit. Besides, it will then be easier to debug.

    You accomplish the sum by adding a new field in the QBE grid that adds the other values together using their field names.

    If you go into SQL view in query design, paste your SQL and then switch to regular design view, you can see a visual representation of what you are trying to build.

    tc

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber
    How do I total WSODV+TSODV+WSTDV, and WSO+TSO+WST and place the results in two feilds at the far right side of the Results Records set.
    oh, that's easy -- see this thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    You didn't want to talk to me any longer!

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    oh, didn't r937's link solve your problem?

Posting Permissions

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