SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL and query script in VB -newbie

    Am trying to write a MSSQL query via a Excel2003 macro in VB.

    I am doing the below query to SUM(A.NO) value in a table A. This works fine until its gets more complicated and I am doing a JOIN and I need to specify 2 variables for the WHERE statement as shown below.

    Script to enter value for row identification

    Dim sCode As String
    sCode = Application.InputBox(Prompt:="Matter Number?", Type:=2)
    strSQL = Replace$(sSQL, "zz", sCode)


    This works fine.

    Now when I join two tables to query a SUM it falls apart as it sums the total values of the table not just the individual value as with the statement below

    sqlstring = "Select A.NO
    sum(B.AMOUNT)as TOTAL
    From A, B

    Where A.NO = 'zz'



    Here is the problem I need to define the NO as "zz" my manual entry via the pop up box and also I need to define NO in both tables A and B. Can someone help me.

    Where A.NO = B.NO

    Hope that makes sense

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    Select 
        A.NO
      , sum(B.AMOUNT) as TOTAL
    From A, B
    Where A.NO = B.NO
    AND A.NO = 'zz'

  3. #3
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah the AND statement thats what I was looking for thank you for taking the time to give me that info.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    don't forget the GROUP BY clause!

    also, AND is not required if you use explicit JOIN syntax (which you should)
    Code:
    SELECT a.no
         , SUM(b.amount) AS total
      FROM a
    INNER
      JOIN b
        ON b.no = a.no
     WHERE a.no = 'zz'
    GROUP
        BY a.no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You really should be using parameters rather than string replacement there. String replacement + SQL are bad.

  6. #6
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the replies I will relook at my code to include the inner join I have not used that statement yet.

    My coding is basic at best but I am producing some good reporrts now from SQL for my company.


    Can you please elaborate or give me an example of parameters instead of string replacement. I just stole and hacked this code I found via google.

    Also if you have any good links or books I would be keen to learn more.

    Cheers

  7. #7
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow got it all working as shown by you guys way cool.

    Thanks


Bookmarks

Posting Permissions

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