need to do a sum of values with out double summing the duplicates.. (I am stuck)

[LordJezo]

need to do a sum of values with out double summing the duplicates.. (I am stuck)

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

because filesystem 1 adds 5 and 1, filesystem 2 adds 4 and 2 only once because I only want to count the first time, file system 3 adds 8 and 6.

(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.

 

[neileg]

Becasue I'm just a hacker, I'd do it this way:
Create a totals query that pulls Server name and installed.
Create another totals query that pulls Server name and used.
Join these queries in a third query that gives you Server name, SumOfinstalled, SumOfused.

 

[theprez]

I believe Neileg (hacker or not) is on the right track. You'll probably need a couple of queries.

The first query (query1) will be a select query that will have one little twist. Go into it and pull it up the SQL code. After the word Select, type Distinct.

SELECT DISTINCT Table1.Server, Table1.FileSystem, Table1.installed, Table1.used
FROM Table1;

Now, create a second summation query (query2) that will be based on query1.
Select the fields Server, Installed and Used. Group by the Server field and Sum the Installed and Used field.

I tested this and I believe it does what you want. If you need additional assistance, I can send you the table and 2 queries I have.

 

[LordJezo]

I believe Neileg (hacker or not) is on the right track. You'll probably need a couple of queries.

The first query (query1) will be a select query that will have one little twist. Go into it and pull it up the SQL code. After the word Select, type Distinct.

SELECT DISTINCT Table1.Server, Table1.FileSystem, Table1.installed, Table1.used
FROM Table1;

Now, create a second summation query (query2) that will be based on query1.
Select the fields Server, Installed and Used. Group by the Server field and Sum the Installed and Used field.

I tested this and I believe it does what you want. If you need additional assistance, I can send you the table and 2 queries I have.

Thanks I took that and did it, but I had to modify your suggestion just a tad..

I can't figure out how to get a second query to be based off a previous query. What I ended up doing was taking the distict statement that you posted and having that query be a "make table" query. Then I had a second table query the second table.

Is there a way to streamline that and put them into one single query?

 

[theprez]

Yes, Queries can be based on other queries. Instead of making a table with Query1, make query 2 based on query 1. In design view when you first design query2, you will have a tab with Table, Query, Both. Choose the Queries Tab and find Query1. Base your Query on this. If you still need help, I have actually did this on another system just to make sure it works. I can put my tables and queries in a database and attach. The SQL version of the second query is this;

SELECT Query1.Server, Sum(Query1.installed) AS SumOfinstalled, Sum(Query1.used) AS SumOfused
FROM Query1
GROUP BY Query1.Server;

 

[neileg]

Yeah, sorry I missed the bit about duplicates in the data. As the Prez says, DISTINCT will eliminate these.

 

[LordJezo]

Yes, Queries can be based on other queries. Instead of making a table with Query1, make query 2 based on query 1. In design view when you first design query2, you will have a tab with Table, Query, Both. Choose the Queries Tab and find Query1. Base your Query on this. If you still need help, I have actually did this on another system just to make sure it works. I can put my tables and queries in a database and attach. The SQL version of the second query is this;

SELECT Query1.Server, Sum(Query1.installed) AS SumOfinstalled, Sum(Query1.used) AS SumOfused
FROM Query1
GROUP BY Query1.Server;

So simple!

Thanks. I will go play with that now.

Can I nest queries in each other?

 

[theprez]

If by nested, you mean have queries based on other queries, the answer is Yes.