DSUM?

[John C.]

I'm using the following expression in a query to return totals for invoices
per customer and keep getting an error with the query displays results.

Expr2: DSum("[actualPrice]","2006 ICP Accounts","[DocNo] =" & [DocNo])

I have a field that is called [actualPrice] in a table "2006 ICP Accounts"
and another field that is called [DocNo] which is like an invoice number.
When I run the query I would like to see the total sales for each customer. I
don't need the total for each invoice [DocNo]. I just need the totals for
each account.

Am I on the wrong track?

 

[Douglas J. Steele]

What's the data type of DocNo? If it's text, you need to put quotes around
the value:

DSum("[actualPrice]","2006 ICP Accounts","[DocNo] ='" & [DocNo] & "'")

If that's not the issue, then what does the error message tell you?

 

[John C.]

Douglas, Now that I have the data in one table how do I only show the
customer one time?

What's the data type of DocNo? If it's text, you need to put quotes around
the value:

DSum("[actualPrice]","2006 ICP Accounts","[DocNo] ='" & [DocNo] & "'")

If that's not the issue, then what does the error message tell you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using the following expression in a query to return totals for
invoices
per customer and keep getting an error with the query displays results.

Expr2: DSum("[actualPrice]","2006 ICP Accounts","[DocNo] =" & [DocNo])

I have a field that is called [actualPrice] in a table "2006 ICP Accounts"
and another field that is called [DocNo] which is like an invoice number.
When I run the query I would like to see the total sales for each
customer. I
don't need the total for each invoice [DocNo]. I just need the totals for
each account.

Am I on the wrong track?

 

[Douglas J. Steele]

I'm sorry, I don't understand the question.

What do you mean by "show the customer one time"? Where are you trying to
show the customer?

If it's in a query, you're pretty much out of luck. If it's in a form, you
can put calculations into the Footer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, Now that I have the data in one table how do I only show the
customer one time?

What's the data type of DocNo? If it's text, you need to put quotes
around
the value:

DSum("[actualPrice]","2006 ICP Accounts","[DocNo] ='" & [DocNo] & "'")

If that's not the issue, then what does the error message tell you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using the following expression in a query to return totals for
invoices
per customer and keep getting an error with the query displays results.

Expr2: DSum("[actualPrice]","2006 ICP Accounts","[DocNo] =" & [DocNo])

I have a field that is called [actualPrice] in a table "2006 ICP
Accounts"
and another field that is called [DocNo] which is like an invoice
number.
When I run the query I would like to see the total sales for each
customer. I
don't need the total for each invoice [DocNo]. I just need the totals
for
each account.

Am I on the wrong track?

 

[John W. Vinson]

I'm using the following expression in a query to return totals for invoices
per customer and keep getting an error with the query displays results.

Expr2: DSum("[actualPrice]","2006 ICP Accounts","[DocNo] =" & [DocNo])

I have a field that is called [actualPrice] in a table "2006 ICP Accounts"
and another field that is called [DocNo] which is like an invoice number.
When I run the query I would like to see the total sales for each customer. I
don't need the total for each invoice [DocNo]. I just need the totals for
each account.

Am I on the wrong track?

Yes. You don't need the DSum at all to get what you describe!

Instead create a Totals query based on the table, grouping by account (a field
that evidently exists in your table but which you have not described) and
summing ActualPrice. The DocNo field (whatever it is, remember we can't see
your database!) would not come into the query at all.

If this doesn't help or if I'm misunderstanding the question, please post with
a bit more detail.

 

[John C.]

Sorry about being vague. Part of my query includes a customer field. When I
run the query below it returns all the invoices for each customer. The new
field created show the total of all invoice. I would like to display each
customer and the total of all their invoices one time.

I'm sorry, I don't understand the question.

What do you mean by "show the customer one time"? Where are you trying to
show the customer?

If it's in a query, you're pretty much out of luck. If it's in a form, you
can put calculations into the Footer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, Now that I have the data in one table how do I only show the
customer one time?

What's the data type of DocNo? If it's text, you need to put quotes
around
the value:

DSum("[actualPrice]","2006 ICP Accounts","[DocNo] ='" & [DocNo] & "'")

If that's not the issue, then what does the error message tell you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm using the following expression in a query to return totals for
invoices
per customer and keep getting an error with the query displays results.

Expr2: DSum("[actualPrice]","2006 ICP Accounts","[DocNo] =" & [DocNo])

I have a field that is called [actualPrice] in a table "2006 ICP
Accounts"
and another field that is called [DocNo] which is like an invoice
number.
When I run the query I would like to see the total sales for each
customer. I
don't need the total for each invoice [DocNo]. I just need the totals
for
each account.

Am I on the wrong track?

 

[John C.]

Again sorry for being vague. I have a table that includes customer data
(name, acct number etc..). It also includes all their puchases. (invoice
numbers, sku # etc.) Each record is based on the Sku#. So one customer can
have one or many invoices which can have one or many items (Sku #). What I
would like to do is display each customer only once and show the sum of all
their purchases regardless of invoices. In otherwords I want to display
total sales for ABC Company. I hope this is little more clear. Thanks for
your help.

I'm using the following expression in a query to return totals for invoices
per customer and keep getting an error with the query displays results.

Expr2: DSum("[actualPrice]","2006 ICP Accounts","[DocNo] =" & [DocNo])

I have a field that is called [actualPrice] in a table "2006 ICP Accounts"
and another field that is called [DocNo] which is like an invoice number.
When I run the query I would like to see the total sales for each customer. I
don't need the total for each invoice [DocNo]. I just need the totals for
each account.

Am I on the wrong track?

Yes. You don't need the DSum at all to get what you describe!

Instead create a Totals query based on the table, grouping by account (a field
that evidently exists in your table but which you have not described) and
summing ActualPrice. The DocNo field (whatever it is, remember we can't see
your database!) would not come into the query at all.

If this doesn't help or if I'm misunderstanding the question, please post with
a bit more detail.

 

[John W. Vinson]

Again sorry for being vague. I have a table that includes customer data
(name, acct number etc..). It also includes all their puchases. (invoice
numbers, sku # etc.) Each record is based on the Sku#. So one customer can
have one or many invoices which can have one or many items (Sku #). What I
would like to do is display each customer only once and show the sum of all
their purchases regardless of invoices. In otherwords I want to display
total sales for ABC Company. I hope this is little more clear. Thanks for
your help.

Then do what I suggested. Create a Query based on your table. Select the
company/customer ID, and the sales value. Change it to a Totals query by
clicking the Greek Sigma icon (looks like a sideways M); Group By the customer
field, and Sum the amount field.