Total Results From Query

[Rick4077]

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

 

[Rick4077]

The only code I have in Access is what I've been trying to piece together from the suggestions I've obtained here in this forum.

In Paradox it's like this . . .

method pushButton(var eventInfo Event)
{This code scans a table with office names and assign each
office name to the variable s and runs the code for each office
in the table.  The asorted variables throughout the code are
stored in tables that are linked to reports.  This code
alliveates the necessity of additional code for each office.
(c) March 21, 1997 Advanced Computer Technologies}
var;----------------Declare Variables-------------
ofc,tc,totaltc	tCursor
Scmls				table     ;All downloaded sales
CBAll				table		 ;Stored Table w/Office Names
CBAllLink		table		 ;Linked table to report (Stats)
s					string
q					query
r					report
i					longint
endVar;-------------End Variable List------------
addAlias("MYDIR","Standard","D:\\Pdoxwin\\Working")
;---------------------------------------------------------------
CBAllLink.attach("CBAllLink")	;This is the Answer Table linked to rpts
if isTable(CBAllLink) then	   ;which is emptied each time script runs
CBAllLink.empty() 			   ;Empty the table
else
errorShow()
endIf
;---------------------------------------------------------------
if Not tc.open(":MYDIR:Scmls") then ;All downloaded sales are in
errorshow()                         ;this table
Return
Endif
;---------------------------------------------------------------
if Not ofc.open(":MYDIR:CBAll") then ;This table stores all
errorshow()                          ;of the offices unique
Return                               ;to the report
Endif
;---------------------------------------------------------------
scan ofc:		
s = ofc."OfcName"  ;gets office name from field OfcName
s=s+".."
;msginfo("Assigned?",s.isAssigned()) ;Test the variable s
    {-------------------------We Sold Ours----------------------------}
  q=

Query
ANSWER: :PRIV:ANSWER.DB

SCMLS.DB | SALEPRICE |
			| CALC SUM AS PRICE, calc count all AS recs |

SCMLS.DB | LISTNAME | SELLNAME     |
			| ~s       | ~s |

EndQuery

	if not q.executeQbe()then
errorShow()
	return
	endif
	  If Not tc.open(":PRIV:Answer") then
	  errorShow()
	  Return
	  endIf
	 if tc.isEmpty() then  	 ;if Tcursor is empty
	 WSO=0             ;variable is zero
	 WSOdv=0           ;variable is zero
	 Else              ;else
	 WSO   =tc.recs*2  ;variable = # of records in query times 2
	 WSOdv =tc.price*2 ;variable = Sum of dollars in query times 2
	 Endif
tc.close()
{---------------------------They Sold Ours------------------------}
q=

Query
ANSWER: :PRIV:ANSWER.DB

SCMLS.DB | SALEPRICE | LISTNAME |
			| CALC SUM AS PRICE, calc count all AS recs | ~s |

SCMLS.DB | SELLNAME |
			| NOT ~s   |

EndQuery
	if not q.executeQbe()then
	errorShow()
	message ("Query error TSO...")
	return
	endif
	 If Not tc.open(":PRIV:Answer") then
		errorShow()
		Message("Open error tCursor TSO...")
		Return
		endIf
	 if tc.isEmpty() then 	;if Tcursor is empty
	 TSO=0            		;variable is zero
	 TSOdv=0          		;variable is zero
	 Else             		;otherwise...
	 TSO   =tc.recs   		;variable = # of records in query
	 TSOdv =tc.price  		;variable = Sum of dollars in query 
	 Endif
tc.close()
{-------------------------We Sold Theirs--------------------------}
q=

Query
ANSWER: :PRIV:ANSWER.DB

SCMLS.DB | SALEPRICE |
			| CALC SUM AS PRICE, calc count all AS recs |

SCMLS.DB | LISTNAME | SELLNAME |
			| NOT ~s   | ~s |

EndQuery
	if not q.executeQbe()then
	errorShow()
	return
	endif
	 If Not tc.open(":PRIV:Answer") then
		errorShow()
		Return
		endIf
	 if tc.isEmpty() then	;if Tcursor is empty
	 WST=0               	;variable is zero
	 WSTdv=0             	;variable is zero
	 Else                	;else
	 WST   =tc.recs      	;variable = # of records in query
	 WSTdv =tc.price     	;variable = Sum of dollars in query
	 Endif
tc.close()
q=
{----------------------Total Listing Dollars----------------------}
Query
ANSWER: :PRIV:ANSWER.DB

SCMLS.DB | SALEPRICE | LISTNAME |
			| Calc Sum All as Listdv | ~s |

EndQuery

  if not q.executeQbe()then
	errorShow()
	return
	endif
		If Not tc.open(":PRIV:Answer") then
		errorShow()
		Return
		endIf
	if tc.isEmpty() then  ;if Tcursor is empty
	listdv = 0        	 ;variable is zero
	Else              	 ;else
	listdv =tc.Listdv 	 ;variable = Sum of dollars in query
	Endif
tc.close()
tt=WSO+TSO+WST
totaldv=WSOdv+TSOdv+WSTdv
  If not totalTc.open ("CBAllLink") then
  errorShow()
  Return
  endIf
  s = ofc."OfcName"
  totalTc.end()     {totaltc. is for tables that store info.}
totalTc.edit()
totalTc.insertAfterRecord()
totalTc."Office"		  = s
;totalTc."Date" 		  = today() - 25
totalTc."WeSoldOurs"	  = WSO
totalTc."TheySoldOurs" = TSO
totalTc."WeSoldTheirs" = WST
totalTc."Total Sales"  = tt
totalTc."List DV"		  = listdv
totalTc."Total DV" 	  = totaldv
totalTc.endedit()
totaltc.close()
endScan
Sort "CBAllLink.db"
	ON	"Total DV"  D
EndSort
If Not exportSpreadsheet("CBAllLink.db","D:\\FLW\\Reports\\CBAllLink.xls", True)
then
errorShow()
endIf
If Not r.open("CBAll") then      ;Open the Report
errorShow()
Return                                   
Else
r.menuAction(MenuReportPageLast)      ;Moveto last page of report
Endif                                    
endMethod{--------------------------------------------------------}

 

[r937]

your query, man, i'd like to see your latest query

you posted a query in the very first post in this thread

after that, you posted only snippets of a query

i don't want to see your code, i want to see your latest query, like in the very first post in this thread, only make sure it's the latest version

just the query, but the whole thing, not just the snippets

i would like to see the entire query

r937.com | rudy.ca

 

[Rick4077]

I placed a (*) Wildcard at the end of each C.OfcName. Will this make a difference in the way the query produces results?

When running this script I get an error that states;

"Syntax Error (missing operator) in query expression in C.OfcName in (S.SellName,C.ListName) GROUP BY C.OfcName"

I've tried everything I can think of but nothing removes the error.

Thanks . . . Rick

select C.OfcName
     , WSODV
     , WSO
     , TSODV
     , TSO
     , WSTDV
     , WST
     , WSODV+TSODV+WSTDV as [Total Dollar Volume]
     , WSO  +TSO  +WST   as [Total Transactions]

 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]

I placed a (*) Wildcard at the end of each C.OfcName. Will this make a difference in the way the query produces results?

yes, a complete difference, those will have to go

did you see my remark "go back to the version with Len()"

i also said "go back to the comma-list FROM clause with the WHERE condition for the match"


r937.com | rudy.ca

 

[PHV]

are you having trouble understanding your own query?

Rudy, just in case you're still curious: thread701-1247925

 

[Rick4077]

You know, this seems extremly difficult in ACCESS as opposed to Paradox.

I just don't get the complexity?

I've got so many suggestions that I'm "dazed and confused."

Rick

 

[r937]

okay, if i had known that, i wouldn't have wasted so much of my time here

thanks, PHV

bye bye, rick

r937.com | rudy.ca

 

[Rick4077]

Listen - I don't blame you . . . however, I'm going to get this no matter what. I DO NOT want to continue with Paradox.

This code calculates some of the offices in C.OfcName correctly, and others off by just a few totals. Don't know why but it's the nearest I've come.

Notice the changed <> expressions?

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.ListName,Len(C.OfcName))<Left(S.SellName,Len(C.OfcName)),S.SalePrice,0)) AS TSODV, Sum(IIf(Left(S.ListName,Len(C.OfcName))<Left(S.SellName,Len(C.OfcName)),1,0)) AS TSO, 

Sum(IIf(Left(S.ListName,Len(C.OfcName))>Left(S.SellName,Len(C.OfcName)),S.SalePrice,0)) AS WSTDV, Sum(IIf(Left(S.ListName,Len(C.OfcName))>Left(S.SellName,Len(C.OfcName)),1,0)) AS WST, 

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;