Query vs. Filter Differnt Results

[Rick4077]

When I run this query (entire query not shown here) I get a set of results that differ by only a few count numbers and dollars, than if I filter the database manually.

Does Access look at numbers and dollars differently in a query? Are numbers, etc., rounded up or down.

Are there query controls that I can set within Access 2003?

,Sum(IIf(S.ListName Like C.OfcName&"*" And S.SellName Not Like C.OfcName&"*",1,0)) AS TSO
,Sum(IIf(S.ListName Like C.OfcName&"*" And S.SellName Not Like C.OfcName&"*",S.SalePrice,0)) AS TSODV

This is a modified suggestion from Rudy.

Thanks . . . Rick

 

[PHV]

Query vs. Filter Differnt Results
Which query ?
Which filter ?
Which results with which data ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Okay . . . lets try it from a different angle.

Out of 16 office names, these three names produce a slightly different answer, ONLY in the calculations that are referrenced by TSODV and TSO. The rest of the query works great.

FIRST TEAM
EVERGREEN REALTY
PRUDENTIAL CALIF REALTY

Here is the entire query, which works very will except for the above referrence.

SELECT 
d.OfcName, 
d.WSODV, 
d.WSO, d.TSODV, 
d.TSO, d.WSTDV, 
d.WST, 
WSO+TSO+WST AS [Total Transactions], 
WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]

FROM [SELECT C.OfcName

,Sum(IIf(S.ListName Like C.OfcName&"*" And S.SellName Like C.OfcName&"*",1,0))*2 AS WSO 
,Sum(IIf(S.ListName Like C.OfcName& "*" And S.SellName Like C.OfcName&"*",S.SalePrice,0))*2 AS WSODV

,Sum(IIf(S.ListName Like C.OfcName&"*" And S.SellName Not Like C.OfcName&"*",1,0)) AS TSO
,Sum(IIf(S.ListName Like C.OfcName&"*" And S.SellName Not Like C.OfcName&"*",S.SalePrice,0)) AS TSODV

,Sum(IIf(S.ListName Not Like C.OfcName&"*" And S.SellName Like C.OfcName&"*",1,0)) AS WST
,Sum(IIf(S.ListName Not Like C.OfcName&"*" And S.SellName Like C.OfcName&"*",S.SalePrice,0)) AS WSTDV

FROM CBAll AS C, SCMLS AS S

GROUP BY C.OfcName ]. AS d;

 

[PHV]

these three names produce a slightly different answer
different from WHAT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Sorry . . .

Different from when I filter the database manually.

FIRST TEAM TSO=168 by query, 170 manually
EVERGREEN REALTY TSO=24 by query, 25 manually
PRUDENTIAL CALIF REALTY TSO=58 by query, 55 manually

Is it the way the records are selected by the query?

I just don't get this. It's only on these three offices. The remaining offices are right-on!

Thanks . . . Rick

 

[PHV]

Any chance you could explain what you do "manually" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Here is another wierd one.

Even though the count is off by no more than a few records, all of the dollar amounts are exactly correct!

Any ideas . . . ?

Rick

 

[Rick4077]

Manual filter . . .

Right click field name "Filter For" box FIRST TEAM* etc.

S.ListName S.SellName
FIRST TEAM* FIRST TEAM*
FIRST TEAM* NOT FIRST TEAM*
NOT FIRST TEAM* FIRST TEAM*

Rick