How to sum the values of duplicate data sets in a table?

[DonKaponne]

Hello,

I am facing the problem how to use in Access the in Excel available "sumif" function. There is a table with duplicate data sets for example bank account numbers, where to every bank account are different revenues over the time. My need is to estimate the sum of revenues for every single bank account number. That means, in a first step the duplicate data sets in the table have to be removed. Then it should be build the sum over the revenues for every bank account number.

Thanks a lot for your advice

[ranman256]

Build a query in access that collects the values you want.
Export / import that query into Excel.

[DonKaponne]

The problem is I don't know how to build this query. It's not an ordinary query for me. Can you "ranman256" post the SQL code of such a query. If there is another solution within Access, please post it here.

I have managed to do the first step, to collect the account numbers without the duplicates and to write them into a new table. Now I need to build new query accessing both tables and use the second table as a condition to sum the revenues from the first table.

Thanks

[ranman256]

I dont know your table structures.
It would be like: Select [Name], Sum([AcctValue]) as TotalAmt from table where [person]='Bob Smith')

[DonKaponne]

I have the table "tblTest" with three fields (ID, Field1, Field2). In Field1 are the account numbers, in Field2 are the revenues to them. So how should be build the query so that been selected all account numbers without duplicated (Field1) and in a second step to sum the revenues to every single account number (Field2).

[June7]

ranman's query will work if you filter to a single person. If you want all:

SELECT [AccountNumber], Sum([AcctValue]) AS TotalAmt FROM table GROUP BY [AccountNumber];

But do you need duplicates removed before or after the data is summed? Why would there be duplicate data? Do you want to provide sample?

[DonKaponne]

I need the duplicates removed before summing the data. As an example- you have many account numbers (say 5.000, it's the Field1), that appear many times. There are different revenues to the account numbers (say 15.000, it's the Field2). So the task is to estimate the total revenue to every account number.

[June7]

Then doesn't sound like duplicate data. What do you mean by 'different' revenues? Each revenue has a code identifier? Have the query summarize by account and revenue code, like:

SELECT [AccountNumber], [RevenueCode], Sum([AmountField]) AS TotalAmt FROM table GROUP BY [AccountNumber], [RevenueCode];

[DonKaponne]

It's not so complicated.

The table (tblTest) looks like:

Field1 Field2 Field3 Field4 Field5
101 100$
102 200$
103 600$
101 340,5$
102 465$
103 500$
104 1200$
101 45$
....

For me are important only Field1 (there is the account number) and Field2 (account turnoover or revenues).

There is duplicate data in term of the account numbers because one and the same account number occuring many times (in the example account number 101 occurs 3 times). In my table "tblTest" gathering Field1 and Field2 are 15.000 data sets in total, yet there are only 5.000 unique account numbers. I wanna estimate the sum of the account turnover to every account number.

[June7]

These are not duplicate records. The account numbers are repeated but the amount values are different.

Summing the amount values for each account is easily done with an aggregate query. I don't understand the calculation you want to do. What do you mean by 'estimate the sum of the account turnover'? What is the formula to do that?

[DonKaponne]

"Summing the amount values for each account is easily done with an aggregate query."
How is to be build such an aggregate query. Could you post the SQL code please.

Maybe I haven't express myself in the correct way.

"The table (tblTest) looks like:

Field1 Field2 Field3 Field4 Field5
101 100$
102 200$
103 600$
101 340,5$
102 465$
103 500$
104 1200$
101 45$
...."

As a result, the query should give out:

Field1 Field2
101 (uniques account numbers) 485,5 $ (sume of the amount values (100+ 340,5 +45) for the account number 101 )
102 ....
...
103
...

Field1 should be without duplicate acount numbers


I have tried with that SQL code:

SELECT Distinct ID, Field1, Field2
FROM tblTest
WHERE [Field1] HAVING COUNT(*) >= 1
SUM [Field2] as TotalAmt FROM tblTest
GROUP BY ID

but it doesn't work

[June7]

We have already posted several examples of aggregate query. See post number 6.

Use the query builder.