DSUM Function

[Jasper Recto]

I have a query where I would like a colum that gives me the domain some for
a each group.

However, within that group there are duplicate lines that I don't want to
include in the sum.

Is it possible to do a DSum thats kind of like a Sum If in excel where it
will only sum if it meets a certain criteria?

For example,

Week Of: Order # Order Line Order qty Shipqty Shipdate
DSum
06/01/08 1000 1 50 10
6/2/08 70
06/01/08 1000 1 50 10
6/3/08 70
06/01/08 1000 1 50 30
6/4/08 70
06/01/08 2000 2 20 10
6/4/08 70
06/01/08 2000 2 20 10
6/4/08 70


In this scenario, the sum for week of 6/1 is the 50 and 20 from order 1000
and 2000. NOT the sum of all 5 lines.

Any ideas?

Thanks,
Jasper

 

[Traveler]

Jasper,

Use "Like" and "NOT Like" in the criterior section of your query to
include/exclude values. Also, in the query properties you can turn off
duplicate entries. See help section for how to format your criterion using
the Like, NOT Like.

-T

 

[John W. Vinson/MVP]

I have a query where I would like a colum that gives me the domain some for
a each group.

However, within that group there are duplicate lines that I don't want to
include in the sum.

Is it possible to do a DSum thats kind of like a Sum If in excel where it
will only sum if it meets a certain criteria?

For example,

Week Of: Order # Order Line Order qty Shipqty Shipdate
DSum
06/01/08 1000 1 50 10
6/2/08 70
06/01/08 1000 1 50 10
6/3/08 70
06/01/08 1000 1 50 30
6/4/08 70
06/01/08 2000 2 20 10
6/4/08 70
06/01/08 2000 2 20 10
6/4/08 70


In this scenario, the sum for week of 6/1 is the 50 and 20 from order 1000
and 2000. NOT the sum of all 5 lines.

Any ideas?

You will probably need to base a query on another query: the first to
extract one record per set of duplicates, and the second to sum the
results from the first. If the data is in fact as you describe,
there's a major problem with your table design! Why do you have
duplicate records in the first place? Does this table have any other
fields - such as a Primary Key?

What you can do is first create a query selecting all these fields and
view the query's properties. Set the "Unique Values" property to Yes
(the SQL will be something like

SELECT DISTINCT [Week Of:], [Order #], [Order Line], [Order qty],
[Shipqty] [Shipdate], [DSum] FROM yourtable;

Save this as (say) qryDDup. Then create a second query, a Totals query
based on qryDDup, setting the Group By and Sum operators on the totals
row to give you the grouping you want.