Help with Summing Duplicates

[Hadi]

Hello,

I have a query that combines 2 tables. one table has a field called Doc ID
and the other have a field called Cost Code. The relationship is one-to-many
where I have Many Cost Codes that rollup to one Doc ID. I am trying to
create a report from the query to show the total budgeted hours. I have hours
by Doc ID and not by Cost Code (kind of backwards). When I create the query,
i have one record of Doc ID's for each Cost Code. example,

Doc ID Cost Code Budget Hours
1 X 20
1 Y 20
2 A 30
2 B 30

So the total for my budget hours should be 20+30=50 not 100 which was the
query or the report come up with. How can I make the query or report add the
numbers for the Doc ID(which is my grouping band in the report) and not
duplicate the value.

Help is always appreciated

Thank you

 

[JK]

Hadi
Use the DSum() function

TotalCosts=Dsum("[Budget Hours]","TheTableThatHaveBudgetHours", _
"[SomeCriteria]")

Regards/JK




| Hello,
|
| I have a query that combines 2 tables. one table has a field called Doc ID
| and the other have a field called Cost Code. The relationship is
one-to-many
| where I have Many Cost Codes that rollup to one Doc ID. I am trying to
| create a report from the query to show the total budgeted hours. I have
hours
| by Doc ID and not by Cost Code (kind of backwards). When I create the
query,
| i have one record of Doc ID's for each Cost Code. example,
|
| Doc ID Cost Code Budget Hours
| 1 X 20
| 1 Y 20
| 2 A 30
| 2 B 30
|
| So the total for my budget hours should be 20+30=50 not 100 which was the
| query or the report come up with. How can I make the query or report add
the
| numbers for the Doc ID(which is my grouping band in the report) and not
| duplicate the value.
|
| Help is always appreciated
|
| Thank you

 

[Hadi]

thank you JK. the Dsum did it. It took me a while to figure out how put in
the right stuff in the statement. I had to create another query without the
duplicates.

I'd like to know if i can do this directly from the original table. The
table that has Budget Hours has more Doc ID records than my query so what
would be the syntax to the [some criteria] part if I want to say Dsum Budget
Hours where Doc IDs in the table that has budget hours equal the ones the are
in the query. so basically how can i say "only add the budget hours from the
original table from the Doc IDs that exisit in my query"

Hadi

 

[JK]

Hi Hadi,

You can apply all the domain aggregate functions (Dlookup,DCount,DSum etc)
to a table or a query. If your query includes *only* the records that you
need (without duplicates) you base your DSum on the query without a
criteria. If you want to base it on a table, the criteria is the same
criteria that you use to create such query.

Not knowing the structure of your tables and the criteria to apply I cannot
give you exact syntax. But here is an example:

Suppose that you want to sum the budget hours for July:

TotalBudgetHours=Dsum("[Budget Hours]","TableName", _
"[SomeMonthField]=7")

If instead of 7 you want to use the current month, whatever it is, your
criteria will be:

"[SomeMonthField]= " & Month(Date())

Regards/JK



| thank you JK. the Dsum did it. It took me a while to figure out how put in
| the right stuff in the statement. I had to create another query without
the
| duplicates.
|
| I'd like to know if i can do this directly from the original table. The
| table that has Budget Hours has more Doc ID records than my query so what
| would be the syntax to the [some criteria] part if I want to say Dsum
Budget
| Hours where Doc IDs in the table that has budget hours equal the ones the
are
| in the query. so basically how can i say "only add the budget hours from
the
| original table from the Doc IDs that exisit in my query"
|
| Hadi
|
| "JK" wrote:
|
| >
| > Hadi
| > Use the DSum() function
| >
| > TotalCosts=Dsum("[Budget Hours]","TheTableThatHaveBudgetHours", _
| > "[SomeCriteria]")
| >
| > Regards/JK
| >
| >
| >
| >
| > | > | Hello,
| > |
| > | I have a query that combines 2 tables. one table has a field called
Doc ID
| > | and the other have a field called Cost Code. The relationship is
| > one-to-many
| > | where I have Many Cost Codes that rollup to one Doc ID. I am trying
to
| > | create a report from the query to show the total budgeted hours. I
have
| > hours
| > | by Doc ID and not by Cost Code (kind of backwards). When I create the
| > query,
| > | i have one record of Doc ID's for each Cost Code. example,
| > |
| > | Doc ID Cost Code Budget Hours
| > | 1 X 20
| > | 1 Y 20
| > | 2 A 30
| > | 2 B 30
| > |
| > | So the total for my budget hours should be 20+30=50 not 100 which was
the
| > | query or the report come up with. How can I make the query or report
add
| > the
| > | numbers for the Doc ID(which is my grouping band in the report) and
not
| > | duplicate the value.
| > |
| > | Help is always appreciated
| > |
| > | Thank you
| >
| >
| >