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

    Unanswered: Multiple [Enter City Code] entries

    Here is what I have that works great for ONE city code . . .

    Code:
    SELECT tblCARETSData.ListOfficeName
    , Sum(tblCARETSData.ClosePrice) AS [Dollar Volume Closed]
    , tblCARETSData.City
    FROM tblCARETSData
    WHERE (((TblCARETSData.ClosingDate) Between [Enter Starting Date] And [Enter Edning Date]) AND ((TblCARETSData.County)=[Enter County Name] ))
    GROUP BY tblCARETSData.ListOfficeName, tblCARETSData.City
    HAVING (((tblCARETSData.City)=[ENTER CITY CODE]))
    ORDER BY Sum(tblCARETSData.ClosePrice) DESC;
    I would like to allow the end user to enter about six (or less) total city codes.

    Thanks . . . Rick

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Answer to Multiple parameter entries is . . .

    I figured it out . . .

    HAVING (((tblCARETSData.City)=[ENTER 1ST CITY CODE] Or (tblCARETSData.City)=[ENTER 2ND CITY CODE] Or (tblCARETSData.City)=[ENTER 3RD CITY CODE] Or (tblCARETSData.City)=[ENTER 4TH CITY CODE]))

    This works very well.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    This works very well.
    except of course that those conditions need to be in the WHERE clause

    and then there's those stupid brain-dead ms access parentheses, sigh

    try this, and let me know if it works --
    Code:
    SELECT ListOfficeName
         , SUM(ClosePrice) AS [Dollar Volume Closed]
      FROM tblCARETSData
     WHERE ClosingDate BETWEEN [Enter Starting Date] 
                           AND [Enter Edning Date] 
       AND County = [Enter County Name] 
       AND City IN ( [ENTER 1ST CITY CODE]
                   , [ENTER 2ND CITY CODE]
                   , [ENTER 3RD CITY CODE]
                   , [ENTER 4TH CITY CODE] )
    GROUP 
        BY ListOfficeName
    ORDER 
        BY SUM(ClosePrice) DESC
    isn't that a whole lot easier to read?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Yep - it floats!

    I'm working near page 31 in your book trying things.

    This is the first time I've constructed a query in SQL in lieu of datasheet design view.

    I like it better.

    Thanks Rudy.

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Indents in SQL

    Rudy - how do you get the nice indents in your SQL?

    It's much easier to follow and understand.

    Rick

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    Rudy - how do you get the nice indents in your SQL?
    line breaks and spaces
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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