Trying to perform a sum query 2

[kodr]

I'm having trouble trying to perform a 'sum' query. My code is in Excel, and I'm trying to sum the totals of all records that have a certain region code, from an Access database.

I'm using MS ADO 2.8 as a reference, and here is my code so far..

Sub Global_Report(ByVal sOutFileName As String)
Dim iIndex As Integer
Dim iIndex2 As Integer
Dim dRegionTotals(10) As Double

Set Conn = New ADODB.Connection
Set RS = New ADODB.Recordset    '   Query for RegionCodes
Set RS1 = New ADODB.Recordset   '   Query for records within RegionCodes

With Conn
    .Provider = "Microsoft.jet.oledb.4.0"
    .ConnectionString = "data source = c:\OMTRU_Report\Config\OMTRU.mdb"
    .CursorLocation = adUseClient
    .Open
End With

    iIndex2 = 1
    sOutFileName = sOutFileName & "Global.xls"

    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=sOutFileName _
        , FileFormat:=xlNormal, Password:="", writerespassword:="" _
        , ReadOnlyRecommended:=False, CreateBackup:=False


    RS.Open "SELECT * FROM [State_Region]", Conn, adOpenStatic, adLockOptimistic
    
    For iIndex = 1 To RS.RecordCount    '   Problem below!!!
        RS1.Open "SELECT SUM(TRU) as total FROM [Current_Recs] WHERE [State_Region]=" & RS.Fields(7).Value
        dRegionTotals(iIndex2) = RS1("total")
        iIndex2 = iIndex2 + 1
        RS.MoveNext
        RS1.Close
    Next iIndex
    
RS.Close

Set RS = Nothing
Set RS1 = Nothing
Set Conn = Nothing
End Sub
[\code]

I know the following two lines don't work, but I'm at a loss here.  Does anyone have any suggestions?
[code]
        RS1.Open "SELECT SUM(TRU) as total FROM [Current_Recs] WHERE [State_Region]=" & RS.Fields(7).Value
        dRegionTotals(iIndex2) = RS1("total")
[\code]

And finially, is there a better way to interface with Access type databases?  I've found a lot of posts about using stored queries and was wondering if there's a better technique.

Thanks.

 

[SkipVought]

Hi,

    Dim sSQL As String
    
    sSQL = "SELECT SUM(TRU) as total "
    sSQL = sSQL & "FROM [Current_Recs] "
    sSQL = sSQL & "WHERE [State_Region]='" & RS.Fields(7).Value & "'"
    
    RS1.Open sSQL

you need tics around your state region value.

Skip,

_[red][/red]

 

[PHV]

And why not simply a SINGLE Recordset with a SQL like this ?
strSQL = "SELECT S.SomeField, S.AnotherField, Sum(C.TRU) AS total" _
& " FROM State_Region AS S INNER JOIN Current_Recs AS C ON S.KeyField=C.State_Region" _
& " GROUP BY S.SomeField, S.AnotherField"

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

 

[kodr]

Thanks to both of you.

How would I assign the sum(tru) to a variable?

 

[PHV]

In my above example it would be the 3st item of the Recordset.Fields collection.

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

 

[kodr]

Just trying to work out the logic here in my mind. I've only done real simple SQL statements before.

Are S and C declared as recordsets? And the INNER JOIN creates the new recordset of C?

And in the end I could have something like 'iTotal = c.fields(0).value' ?

 

[kodr]

Sorry about my above question. I can see I was way off base.

Here's what I've got for testing..

sSQL = "SELECT S.RegionCode, SUM(S.TRU), AS total FROM Current_Recs AS S"

RS.Open sSQL, Conn, adOpenStatic, adLockOptimistic

After reviewing my table structure and logic, I don't need the second table, sorry. But I will be looking into JOIN's for other parts of my program, so thanks for that.

When I step through the code I get "You tried to execute a query that does not include the specified expression 'RegionCode' as part of an agregate function.

 

[PHV]

S and C are alias for tables names.
You'll instantiate a single recordset giving you as rs.fields(2) the total for region in rs.fields(0) and rs.fields(1)
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

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

 

[kodr]

Thank you. Exactly what I needed.

I was also making my original query more complex then I needed.

 

[PHV]

If only one table suffices:
sSQL = "SELECT RegionCode, Sum(TRU) AS total FROM Current_Recs GROUP BY RegionCode"

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

 

[kodr]

That did it.

Again, thanks for the link on INNER JOINS. This will make things alot simpler going forward. I'm tired of maintaining hundreds of lines of code that can be replaced by simple SQL statements. Databases can be a wonderful thing.

 

[kodr]

Last question (I think.)

What's wrong with this one:

sSQL = "SELECT C.RegionName, S.RegionCode, SUM(S.TRU) AS total FROM Current_Recs AS S " & _
"INNER JOIN Region_Code AS C ON S.RegionCode=C.RegionCode GROUP BY S.RegionCode"

It works if I remove the C.RegionName portion.

I get "Item cannot be found in the collection corresponding to the requested name or ordinal." at RecordSet.Open

 

[SkipVought]

    sSQL = "SELECT "
    sSQL = sSQL & "  C.RegionName"
    sSQL = sSQL & ", S.RegionCode"
    sSQL = sSQL & ", SUM(S.TRU) AS total "
    sSQL = sSQL & "FROM Current_Recs      AS S "
    sSQL = sSQL & "INNER JOIN Region_Code AS C "
    sSQL = sSQL & "   ON S.RegionCode=C.RegionCode "
    sSQL = sSQL & "GROUP BY "
   [b] sSQL = sSQL & "  C.RegionName"[/b]
    sSQL = sSQL & ", S.RegionCode"

Skip,

_[red][/red]

 

[kodr]

Never would have suspected that. I thought the Group by was optional.

Thanks.

 

[PHV]

You have to GROUP by all NON aggregated fields in the SELECT list.

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