By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,644 Members | 2,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,644 IT Pros & Developers. It's quick & easy.

SQL SUM Function

P: 15
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?

Expand|Select|Wrap|Line Numbers
  1. Private Function CreateRecords() As ADODB.Recordset
  2.     Dim conLog As ADODB.Connection
  3.     Dim rsLog As ADODB.Recordset
  4.  
  5.     'Open a connection to the Database
  6.     Set conLog = New ADODB.Connection
  7.     conLog.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DutyLog.mdb; Persist Security Info=False"
  8.     conLog.Open
  9.  
  10.     'Find Log Entries
  11.     Set rsLog = conLog.Execute("SELECT DISTINCTROW tblWeight.Courier, tblWeight.Weight As [Sum Of Weight] " & _
  12.                                "FROM tblWeight " & _
  13.                                "WHERE tblWeight.EventDate Between #" & calWeight1 & "# AND #" & calWeight2 & "#")
  14.  
  15.     'Creates a disconnected recordset
  16.     Dim rsTemp As New ADODB.Recordset
  17.  
  18.     With rsTemp
  19.  
  20.         ' Create Recordset Fields
  21.         .Fields.Append "Name", adVarChar, 40
  22.         .Fields.Append "Weight", adInteger
  23.         .CursorType = adOpenKeyset
  24.         .LockType = adLockOptimistic
  25.         .Open
  26.         ' Add a new item record
  27.         Do While Not rsLog.EOF
  28.             .AddNew
  29.             ![Name] = rsLog(0)
  30.             ![Weight] = rsLog(1)
  31.             .Update
  32.             rsLog.MoveNext
  33.         Loop
  34.  
  35.     End With
  36.  
  37.     Set CreateRecords = rsTemp
  38.  
  39.     'Close the connection to the Database
  40.     conLog.Close
  41. End Function
Dec 24 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 5K+
P: 8,434
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.
Dec 24 '07 #2

Expert 5K+
P: 8,434
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...

Expand|Select|Wrap|Line Numbers
  1. SELECT tblWeight.Courier, Sum(tblWeight.Weight) AS [Sum Of Weight] FROM tblWeight
  2. WHERE tblWeight.EventDate Between #1/1/1980# And #2/2/1980#
  3. GROUP BY tblWeight.Courier;
  4.  
Dec 24 '07 #3

P: 15
Great! Worked perfectly. Thank You
Dec 24 '07 #4

Expert 5K+
P: 8,434
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.)
Dec 27 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.