Trying to consolidate information with different value types

[Silver_Rain_007]

Okay so I have a database that, vaguely looks like the following

NAME Value Category A Value Category B Value Category C

Name A -50 0 0
Name A 0 50 0
Name B -100 0 0
Name B 0 75 10
Name B -25 0 0
Name C -100 0 0

Etc. etc.

What I'd like is to find a query that will first, generate a Category D which should be a total of all the values in the three different categories and consolidates the names to one line. So it should spit something like this out.

NAME Value Category A Value Category B Value Category C Total
Name A -50 50 0 0
Name B -125 75 10 -40
Name C -100 0 0 -100

After this, I'd like to have another query that will only display the values that are NOT zero.

NAME Val Cat A Val Cat B Val Cat C Total
Name B -125 75 10 -40
Name C -100 0 0 -100

I know there has to be a way to do this, I remember at a previous position there being a query that did something similar and I remember using it but not remembing how it was done. Any and all help is so appreciated. Thank you!

EDIT: I hope its still understandable the forum didn't keep my spacing and such when i posted. If I can provide any extra clarification also please let me know.

[rpeare]

Create a query based on your table (let's assume your example data is in table tblTest)

Click on the SIGMA button (looks like a capital E on your ribbon bar)

Add the NAME field
Add the VALUE field
Add the CATEGORY A, CATEGORY B and CATEGORY C fields
in the CATEGORY columns in the TOTALS row of the query design put SUM
Create a column with the formula:
TOTAL: [Category A] + [Category B] + [Category C]
in the TOTALS row of the query design put SUM for this field as well
in the CRITERIA of the TOTAL column put <>0

[Silver_Rain_007]

I'm able to make a total field following the above. But I still need to consolidate people with the same name into one line with their totals by line also consolidated.

Right now I have

Name A B C Total

Name 1 100 0 0 100
Name 1 0 -50 0 -50

I want this to be
Name 1 100 0 0 50
Name 1 0 -50 0 50

I'd even be okay if like in the above example the total that appeared was the total for all the amounts for everyone with the same name.

Would it be easier if I maybe consolidated the different values into a single variable
Name A Total
Name 1 100 50
Name 1 -50 -50

[rpeare]

The example I gave you will group it by person.

Make a query add the person click the SIGMA BUTTON it looks like a capital E
you'll notice the TOTALS LINE says GROUP BY

Run the query.

The query will show your list of unique people

From there add your amount fields and do whatever you want with them, just make sure the TOTALS line says SUM or EXPRESSION or the grouping probably won't be what you want.