Summing query or DSUM?

[Paul Smith]

Dear All

I have a database with a table containing the value of several
purchases by several customers. I want to create a single form to show
the name of a customer and the total of his/her purchases. Of course, I
could use the function DSUM, but I am speculating whether I could
instead use a summing query and using it to create my form with the
possibility of updating the name of customer from the form. Is that
possible?

Thanks in advance,

Paul

 

[Barry Gilbert]

If you build a query, you would group by customer and sum the
purchases. This would not be an updateable query. However, you could do
an unbound update to change the customer name. It would require some
code.

Are your customers listed in a separate table from the purchases? This
would be the best design. Then you would update the customer name there
and not in the purchases table.

 

[Paul Smith]

Are your customers listed in a separate table from the purchases? This

would be the best design. Then you would update the customer name there
and not in the purchases table.

Thanks, Barry. You are right: customers are listed in a separate table.
So, are you telling me that I cannot update, without writing some code,
the customers names from the form, when the form is created through a
summing query?

Paul

 

[Barry Gilbert]

That's right. But the code should be fairly simple. Behind a button,
put the following:

Private Sub cmdChangeCust_Click()
Dim strSQL as String
Dim strNewCustName as String
strNewCustName=Inputbox("Enter the new customer name","Change
Customer")
' In case they cancel, test for a null string.
If strNewCustName<>vbNullString Then
strSQL = "Update tblCustomers Set CustomerName = '" &
strNewCustName & "' WHERE tblCustomers.CustomerName = '" &
Me.txtOldCustomerName & "'"
DoCmd.RunSql strSQL
Endif

End Sub

You'll have to change the names of the tables, fields, controls, etc.
to match your database.

HTH,
Barry

 

[Guest]

A query with a DSUM field will be updateable, and the DSUM will be
evaluated on each line.

This will make the 'whole query' slow - it will take a long time to get
to the last record if you have a lot of records. But if you open the
query in datasheet view, the DSUM field won't even be evaluated
unless it is displayed, and anyway the first record will come right
up.

On the other hand, a summation query will evaluate the whole query
before returning the first record. If you have a lot of records, you
may have to wait a while before you see anything. If you are only
interested in one record, you need to set the criteria first, so that
you don't wait until all the records are summed. But if you need to
have the sum down the whole table, in the end this will be much
faster.

(david)

 

[Paul Smith]

That's right. But the code should be fairly simple. Behind a button,

put the following:


Private Sub cmdChangeCust_Click()
Dim strSQL as String
Dim strNewCustName as String
strNewCustName=Inputbox("Enter the new customer name","Change
Customer")
' In case they cancel, test for a null string.
If strNewCustName<>vbNullString Then
strSQL = "Update tblCustomers Set CustomerName = '" &
strNewCustName & "' WHERE tblCustomers.CustomerName = '" &
Me.txtOldCustomerName & "'"
DoCmd.RunSql strSQL
Endif


End Sub


You'll have to change the names of the tables, fields, controls, etc.
to match your database.

Thanks, Barry. I was speculating that maybe creating a second query (to
insert in the form) combining the table customers and the summing query
would do the trick, but apparently it does not.

Paul