If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DSUM?
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? |
#2
|
|||
|
|||
DSUM?
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 http://I.Am/DougSteele (no e-mails, please!) "John C." wrote in message ... 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? |
#3
|
|||
|
|||
DSUM?
Douglas, Now that I have the data in one table how do I only show the
customer one time? "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "John C." wrote in message ... 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? |
#4
|
|||
|
|||
DSUM?
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 http://I.Am/DougSteele (no e-mails, please!) "John C." wrote in message ... Douglas, Now that I have the data in one table how do I only show the customer one time? "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "John C." wrote in message ... 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? |
#5
|
|||
|
|||
DSUM?
On Mon, 8 Dec 2008 08:39:02 -0800, John C.
wrote: 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 [MVP] |
#6
|
|||
|
|||
DSUM?
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. "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "John C." wrote in message ... Douglas, Now that I have the data in one table how do I only show the customer one time? "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "John C." wrote in message ... 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? |
#7
|
|||
|
|||
DSUM?
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. "John W. Vinson" wrote: On Mon, 8 Dec 2008 08:39:02 -0800, John C. wrote: 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 [MVP] |
#8
|
|||
|
|||
DSUM?
On Mon, 8 Dec 2008 10:32:22 -0800, John C.
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|