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 want to create a new field with a query, and sum the records in an existing
field, based on the criteria of another field. In other words, let's say I have an Invoice# field and an InvAmt field. How do I create a 3rd field in a query that totals all InvAmts for each instance that a specific Invoice# appears in the Invoice# field? Would it be a DSUM? Or just a SUM? I know that it's SUMIF as an Excel formula, but I don't see that in Access. Is there an equivalent? Thanks for your time!! -- GD |
#2
|
|||
|
|||
DSUM??
Yes, use DSum domain function.
-- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "GD" wrote in message ... I want to create a new field with a query, and sum the records in an existing field, based on the criteria of another field. In other words, let's say I have an Invoice# field and an InvAmt field. How do I create a 3rd field in a query that totals all InvAmts for each instance that a specific Invoice# appears in the Invoice# field? Would it be a DSUM? Or just a SUM? I know that it's SUMIF as an Excel formula, but I don't see that in Access. Is there an equivalent? Thanks for your time!! -- GD |
#3
|
|||
|
|||
DSUM??
Thanks, Ken! Can you give me the exact expression? I've tried many
variations with no luck. -- GD "Ken Snell (MVP)" wrote: Yes, use DSum domain function. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "GD" wrote in message ... I want to create a new field with a query, and sum the records in an existing field, based on the criteria of another field. In other words, let's say I have an Invoice# field and an InvAmt field. How do I create a 3rd field in a query that totals all InvAmts for each instance that a specific Invoice# appears in the Invoice# field? Would it be a DSUM? Or just a SUM? I know that it's SUMIF as an Excel formula, but I don't see that in Access. Is there an equivalent? Thanks for your time!! -- GD |
#4
|
|||
|
|||
DSUM??
Can you be specific about the data/fields? Your first post suggests that the
expression might be this: =DSum("InvAmt", "NameOfTheTable", "[Invoice#]=" & [Invoice#]) The above assumes that you have a field or control named Invoice# on your form and that that field/control contains the invoice number of interest, and that that field/control is a numeric field. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "GD" wrote in message ... Thanks, Ken! Can you give me the exact expression? I've tried many variations with no luck. -- GD "Ken Snell (MVP)" wrote: Yes, use DSum domain function. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "GD" wrote in message ... I want to create a new field with a query, and sum the records in an existing field, based on the criteria of another field. In other words, let's say I have an Invoice# field and an InvAmt field. How do I create a 3rd field in a query that totals all InvAmts for each instance that a specific Invoice# appears in the Invoice# field? Would it be a DSUM? Or just a SUM? I know that it's SUMIF as an Excel formula, but I don't see that in Access. Is there an equivalent? Thanks for your time!! -- GD |
Thread Tools | |
Display Modes | |
|
|