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

    Unanswered: Count is doubling in query???

    For some reason I'm not able to figure this out.

    This line of code (The first line of the below query) simply counts records in a query.
    Code:
     SELECT C.OfcName, Count(S.ListOfficeName) AS TotalRecs
    But for some reason it is doubling the count. It should be 1220 but it displays 2440 when I place the field in a subReport.

    Code:
     SELECT C.OfcName, Count(S.ListOfficeName) AS TotalRecs
    , Avg(S.SoldPrice) AS AvgPrice, Sum(S.SoldPrice) AS ListingDollarVolume
    , Sum(IIf(S.ListOfficeName Like C.OfcName & "*" And S.SellOfficeName Like C.OfcName & "*",1,0))*2 AS WSO
    , Sum(IIf(S.ListOfficeName Like C.OfcName & "*" And S.SellOfficeName Like C.OfcName & "*",S.SoldPrice,0))*2 AS WSODV
    , Sum(IIf(S.ListOfficeName Like C.OfcName & "*" And Nz(S.SellOfficeName) Not Like C.OfcName & "*",1,0)) AS TSO
    , Sum(IIf(S.ListOfficeName Like C.OfcName & "*" And Nz(S.SellOfficeName) Not Like C.OfcName & "*",S.SoldPrice,0)) AS TSODV
    , Sum(IIf(S.ListOfficeName Not Like C.OfcName & "*" And S.SellOfficeName Like C.OfcName & "*",1,0)) AS WST
    , Sum(IIf(S.ListOfficeName Not Like C.OfcName & "*" And S.SellOfficeName Like C.OfcName & "*",S.SoldPrice,0)) AS WSTDV
    , WSO+TSO+WST AS [Total Transactions]
    , Sum(IIf(S.ListOfficeName Like C.OfcName & "*",S.SoldPrice,0)) AS [Listing Dollar Volume]
    , WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]
    FROM tblNRTCOMPOfcs_sdcity AS C, qryNRTCity_sdcity AS S
    GROUP BY C.OfcName
    ORDER BY 13 DESC;

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    A small table with names in it that the query executed had the same name in it twice!!!

    Never Mind . . .

Posting Permissions

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