Summarize Unique Values Only

[adub2]

Hello everyone...I have a budget database with a bud_acct table and an expense table. I have a field org_bud_amt (expense table) that is a lookup. I also created a summary report that is summarized by fiscal year, department and budget account. The totals on the report are correct but I am trying to summarize only the unique values from the original budget amount field instead of each occurrence. Any help is appreciated.

[Søren Dyhr]

Before going into sorting and preview mode, do this:

Enter Find Mode [  ] 

Set Field [ Untitled::itemID; "!" ] 

Omit Record 

Perform Find [  ] 

...to make an omitted request for duplicates!

--sd

[adub2]

I tried the script steps that you provided I have attached a copy of the report. The first column is the field that I am having a problem with. If I omit records then the expense amount will not be correct. Is there a way to just add the unique values from the original budget amount field only and display that value in the trailing subsummary

[comment]

Your question is not clear enough. What is this field, why is it a lookup, from where does it lookup the data, and - most importantly - is the field's value the same for all records in the same subcategory (I believe that would be the budget account for that year?)?

Offhand, it seems like all these numbers should come from another table of BudgetItems, where each budget account has a record for each year.

[adub2]

Each department has 5 accounts they can choose from. Each account has the original budget amount stored in the budget account table. The original budget amount field value is the same for all records depending on what account they choose. The original field is a look up because I am using a calculation to summarize across records the total after a new expense is added.

I do have a budget summaries table which has the budget account information broken down into another sub-category. The report that I am working on is displaying data from the top level account and not the sub account.

[comment]

I don't get this part (in particular)

The original field is a look up because I am using a calculation to summarize across records the total after a new expense is added.

Seems to me it is precisely this calculation that isn't working?

Anyway, I see two options here: either divide the common amount by the count of records in category, before summarizing it (so that the summarized total eventually reaches the original amount), or (preferably, IMHO) use the data directly from the source (see attached example).

SpendingVsBudget.fp7.zip

[adub2]

Its working now. Thank everyone for their fast responses and thanks for the example comment. It's alot easier to use the data from the source as opposed to what I was trying to do.