Any access gurus in here? I need to do a sum of values with out summing the duplicates..

[LordJezo]

Example..

Table has 4 fields.

Server name, file system, installed, used.

There are duplicates in the server name field and the file system field.

I want to sum up the total file system installed and used on each server, but, if there are duplicate entries in the file system field I only want to count the first one.

Example:

server 1 | filesys1 | 5 | 1
server 1 | filesys2 | 4 | 2
server 1 | filesys2 | 4 | 2
server 1 | filesys3 | 8 | 6

would give as a total:

server name installed used
server 1 | 17 | 9

(dont need the names of the file systems in the total because its a total and not an individual listing)

Any ideas? I can use the find duplicate query wizard to get a report that looks like:

server 1 | filesys1 | 5 | 1
server 1 | filesys2 | 4 | 2
server 1 | filesys3 | 8 | 6

But I can't figure out how to do the sum.

 

[Bulldog13]

I think you re looking for the SELECT DISTINCT statement...

 

[LordJezo]

Originally posted by: Bulldog13
I think you re looking for the SELECT DISTINCT statement...

How do I work that into access.

 

[Descartes]

Originally posted by: LordJezo

Originally posted by: Bulldog13
I think you re looking for the SELECT DISTINCT statement...

How do I work that into access.

If the duplicates have the same installed and used values, then just do this:

select distinct [server name], [file system], sum(installed) as total_installed, sum(used) as total_used
from tablename

Spaces in field names == :evil:

 

[BFG10K]

You don't need to be a guru at all as this is basic stuff. If you don't want to use SQL then make two queries in design view, one that groups the values you want to add and the other that sums the groups from the first query.