Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Total Results From Query

Status
Not open for further replies.
Rick4077

Rick4077

Technical User
Oct 19, 2004
149
US
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
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;

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 so much . . . Rick


 
Sort by date Sort by votes
Code: 
[COLOR=red]select OfcName
     , WSODV
     , WSO
     , TSODV
     , TSO
     , WSTDV
     , WST
     , WSODV+TSODV+WSTDV as total1
     , WSO+TSO+WST       as total2
 from ([/color]
[COLOR=blue]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
  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[/color]
      [COLOR=red] ) as d[/color]



r937.com | rudy.ca
 
Upvote 0 Downvote
Rudy . . . THIS IS AWESOME!!!

When I insert the following the query fails?

Code: 
,Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),S.SalePrice))AS [Listing Dollar Volume]


Any Ideas?

Thanks so much. Rick
 
Upvote 0 Downvote
Also - in the properties fields selection I cannot seem to find how to center the WSO,TSO,WST in the results record set.

When I select Properties those formatting tools are not available.

Thanks . . . Rick
 
Upvote 0 Downvote
Does this mean if C.OfcName = S.ListName AND L.SellName?

Code: 
Sum(IIf(Left(S.SellName,Len(C.OfcName))
              =Left(S.ListName,Len(C.OfcName))
            ,S.SalePrice*2,0)) AS WSODV

Rick
 
Upvote 0 Downvote
no, it means "if the left part of S.SellName (for a length equal to the length of C.OfcName) is equal to the left part of S.ListName (for a length equal to the length of C.OfcName), then use S.SalePrice*2 for WSODV, otherwise use 0 for WSODV"

are you having trouble understanding your own query? ;-)

r937.com | rudy.ca
 
Upvote 0 Downvote
Hi Rudy . . .

These are the only two lines of code that are calculating correctly.

Code: 
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,

I assume that thhese two lines mean [S.SellNamee] AND [S.ListName] both match [C.OfcName]?

The rest of the code produces results but they are all larger than what the number should be.

Thanks . . . Rick



 
Upvote 0 Downvote
Disregard that Rudy . . . wrong two lines of code.

Rick
 
Upvote 0 Downvote

C.OfcName is the client.

First office name in the C.OfcName is XEONON REALTY

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,

ListName Field SellName Field
XEONON REALTY EXONON REALTY WSODV(DOLLAR VOLUME) AND WSO (COUNT RECORDS)

This one calculates perfectly! EXONON REALTY is in both [S.ListName] AND [S.SellName] fields.

Code: 
Sum(IIf(Left(S.SellName,Len(C.OfcName))=AND NOT Left(S.ListName,Len(C.OfcName)),S.SalePrice,0)) AS TSODV, Sum(IIf(Left(S.SellName,Len(C.OfcName))=AND NOT Left(S.ListName,Len(C.OfcName)),1,0)) AS TSO,

ListName Field SellName Field
XEONON REALTY NOT XEONON REALTY TSODV(DOLLAR VOLUME) AND TSO (COUNT RECORDS)

These two lines of code calculate incorrectly!

If a NOT, or the exclusion of the Left(S.ListName,Len(C.OfcName)) is not in the code, it will calculate S.SalePrice and count on those occurances where XEONON REALTY is in the [S.SellName] field!

Code: 
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,

These two lines of code calculate incorrectly!

If a NOT, or the exclusion of the Left(S.SellName,Len(C.OfcName)) is not in the code, it will calculate S.SalePrice and count on those occurances where XEONON REALTY is in the [S.ListName] field!

ListName Field SellName Field
NOT XEONON REALTY XEONON REALTY TSODV(DOLLAR VOLUME) AND TSO (COUNT RECORDS)


Code: 
Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),S.SalePrice)) AS [Listing Dollar Volume]
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 section of code is ok.

I’ve tried to program the "NOT" or exclussion but my code fails every time.

Thanks . . . Rick

 
Upvote 0 Downvote
um, could you perhaps please explain in english what you are trying to accomplish rather than asking for help fixing something that i don't know what it's supposed to do

why are you comparing side-by-side fields? how do you figure the length of the office name is important? what does "not in the code" mean? what are WST and TSO?

r937.com | rudy.ca
 
Upvote 0 Downvote
I'm sorry, this is very confusing but it is correct and it works. It's all in how you count records in the ListName and SellName fields.

WSO - We Sold Our Listing, therefore for every occourance that XEONON REALTY is the ListName and SellName field, IN THE SAME RECORD, we get double the saleprice and twice the count, because we represented both sides of the transaction, buyer and seller!

TSO = They Sold Our Listing, therefore for every occourance that XEONON REALTY is in the ListName and NOT the SellName field, IN THE SAME RECORD, we get the saleprice and the count. They sold on of our listings.

WST = We Sold Their Listing, therefore for every occourance that XEONON REALTY is in the SellName and NOT the ListName field, IN THE SAME RECORD, we get the saleprice and the count. We sold one of their listings.

Whew!

Thanks . . . Rick
 
Upvote 0 Downvote
that makes excellent sense

but i don't understand why you need to use Len(C.OfcName)

it would make sense if C.OfcName was XEONON and S.SellName and/or S.ListName were XEONON REALTY, in which case a straight equality won't work, but if C.OfcName was also XEONON REALTY then there's no need to bother with the length

tell me if there are any unmatched OfcName rows in this query --
Code: 
select OfcName
     , WSODV
     , WSO
     , TSODV
     , TSO
     , WSTDV
     , WST
     , WSODV+TSODV+WSTDV as total_WST
     , WSO  +TSO  +WST   as total_TSO
 from (
SELECT C.OfcName
     , Sum(IIf(S.ListName = S.SellName
              ,S.SalePrice*2,0))       AS WSODV
     , Sum(IIf(S.ListName = S.SellName
              ,1,0))*2                 AS WSO
     , Sum(IIf(S.ListName =  C.OfcName
           and S.SellName <> C.OfcName
              ,S.SalePrice,0))         AS TSODV
     , Sum(IIf(S.ListName =  C.OfcName
           and S.SellName <> C.OfcName
              ,1,0))                   AS TSO
     , Sum(IIf(S.ListName <> C.OfcName
           and S.SellName =  C.OfcName
              ,S.SalePrice,0))         AS WSTDV
     , Sum(IIf(S.ListName <> C.OfcName
           and S.SellName =  C.OfcName
              ,1,0))                   AS WST      
  FROM CBAll AS C
left outer
  join SCMLS AS S
    on C.OfcName In  ( S.SellName,S.ListName )
GROUP 
    BY C.OfcName
       ) as d

r937.com | rudy.ca
 
Upvote 0 Downvote
Error in

Syntax eror missing operator in query expression 'C.OfcName In ( S.SellName,S.ListName )
GROUP
BY C.OfcName
) as d


I couldn't find it?

Rick
 
Upvote 0 Downvote
. . . "it would make sense if C.OfcName was XEONON and S.SellName and/or S.ListName were XEONON REALTY, . . .

This is the case1. It's like a wildcard.

Rick

 
Upvote 0 Downvote
okay then, go back to the version with Len()

and go back to the comma-list FROM clause with the WHERE condition for the match

could you show your latest query in toto and repeat which parts aren't working



r937.com | rudy.ca
 
Upvote 0 Downvote
I'm not sure I understand but the two sections that calculate incorrectly are . . .

TSODV and TSO
(XEONON REALTY is in the ListName and NOT the SellName field,) IN THE SAME RECORD

WSTDV and WST
(XEONON REALTY is in the SellName and NOT the ListName field) IN THE SAME RECORD

Everything else seems to work.

Thanks much! Rick

 
Upvote 0 Downvote
I looked toto up in my Access 2003 Bible but it's not listed

I'm sorry . . . I don't know what toto is . . .

Is that like debugging?

Please remember, I'm VERY new to Access.

I really appreciate all of your help.

Rick
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Locked
  • Question
Query expression negative to a 0
Replies
6
Views
471
dhookom
dhookom
serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
967
strongm
strongm
C
  • Locked
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
410
OzFoxy
OzFoxy
breezett93
  • Question
Query takes 30 seconds - 1 minute to process. Trying to rework it to take 5 or less
Replies
8
Views
450
breezett93
breezett93
serino
  • Locked
  • Question
Delete From Table
Replies
1
Views
814
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top