Unique values for one column, and sum the other column

[Guest]

Hi,

I have a large database. I want to show only unique values for one column,
and I want the values of another column summed. For example:

I have this:

Column X Column Y
----------------------
1 5
1 6
2 7
2 6
3 8
3 9

and I want it to be like this:

Column X Column Y
-------------------------
1 11
2 13
3 17

I'm fairly new to access. Sorry about the simple question.
Thanks in advance

 

[Allen Browne]

1. Create a query that uses this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 2 fields into the query grid.
Accept Group By under X.
Under Y, choose: Sum

 

[Guest]

You need a Totals query. Create a query the includes both columns. Run it to
make sure it gets the raw data you want. Back in design view, go up to View,
Totals. (Personally I think this option should be under Query, but I
digress.) You'll notice a new Total: row in the QBE grid. Leave Column X as
Group By. Change Column Y to sum. For a nice touch, sort on Column X.

 

[Guest]

Thank you for the quick replies.

I did what you said and got the error, "data type mismatch in criteria
expression." I failed to mention that I have a number of columns in this
dataset, not just 2. I have 8 columns. But I still want to do esentially
the same thing: Collapse all the results based only on the unique
(non-duplicate) values of one column, and then sum the corresponding values
of another column.

Thanks again.

 

[Guest]

Ouch. In that case you have a serious normalization problem with that table.
When you need to sum up multiple columns of data into on figure, it's almost
a lock that your table needs to be split out into at least one more table.