SQL and query script in VB -newbie

Databases
1

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 

Select 
    A.NO
  , sum(B.AMOUNT) as TOTAL
From A, B
Where A.NO = B.NO
AND A.NO = 'zz'
3

ah the AND statement thats what I was looking for thank you for taking the time to give me that info.

4

don’t forget the GROUP BY clause!

also, AND is not required if you use explicit JOIN syntax (which you should)

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
5

You really should be using parameters rather than string replacement there. String replacement + SQL are bad.

6

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

wow got it all working as shown by you guys way cool.

Thanks :D:D:D