Help with Summing Duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
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
 
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
 
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
| >
| >
| >
 
Back
Top