SQL SUM Function

[opie]

When I print the data report the output does not display as I intended it to.

I want the Name to be displayed to be unique with the weights shown in MS Access for that indivdual to be SUMmed up.

Any Ideas?

[CODE=vb]Private Function CreateRecords() As ADODB.Recordset
Dim conLog As ADODB.Connectio n
Dim rsLog As ADODB.Recordset

'Open a connection to the Database
Set conLog = New ADODB.Connectio n
conLog.Connecti onString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\DutyL og.mdb; Persist Security Info=False"
conLog.Open

'Find Log Entries
Set rsLog = conLog.Execute( "SELECT DISTINCTROW tblWeight.Couri er, tblWeight.Weigh t As [Sum Of Weight] " & _
"FROM tblWeight " & _
"WHERE tblWeight.Event Date Between #" & calWeight1 & "# AND #" & calWeight2 & "#")

'Creates a disconnected recordset
Dim rsTemp As New ADODB.Recordset

With rsTemp

' Create Recordset Fields
.Fields.Append "Name", adVarChar, 40
.Fields.Append "Weight", adInteger
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.Open
' Add a new item record
Do While Not rsLog.EOF
.AddNew
![Name] = rsLog(0)
![Weight] = rsLog(1)
.Update
rsLog.MoveNext
Loop

End With

Set CreateRecords = rsTemp

'Close the connection to the Database
conLog.Close
End Function[/CODE]

[Killer42]

Doesn't the DISTINCTROW in your SQL mean that the recordset will only return one row for each courier? Or would that be for each unique combination of courier/weight? Either way, it doesn't sound as though it will return all the things you want to add up in your code.

[Killer42]

As I think about this further, it seems as though you're trying to use DISTINCTROW to do the work of the Sum() function. I think the SQL probably should be set up a bit more like...

SELECT tblWeight.Courier, Sum(tblWeight.Weight) AS [Sum Of Weight] FROM tblWeight
WHERE tblWeight.EventDate Between #1/1/1980# And #2/2/1980#
GROUP BY tblWeight.Courier;

[opie]

Great! Worked perfectly. Thank You

[Killer42]

Great! Worked perfectly. Thank You

Excellent! Glad we could help. :)

(I actually got the query designer in MS Access to build the SQL for me - it's much easier that way.)